Thread: postgres_fdw and defaults
I know we've discussed this before, but I have just had the unpleasant experience of trying to get around the difficulty of inserting into a foreign table with a serial field, surely a common enough scenario that we should try to deal with it better. The solution of using a local sequence really doesn't work, as there might be multiple users of the table, as there will be in my scenario. I opted instead to get a value from the foreign sequence explicitly before inserting, but that's pretty ugly. So I am wondering (without having looked at all closely at it) if we could set an option to tell the FDW that we want the foreign default to be used instead of a local one. Is the difficulty that we don't know if a value has been explicitly supplied or not? Maybe we could have some magic value that we could use instead ('foreign_default'?). I'm just throwing out ideas here, but this is really a wart that could well do with attention. cheers andrew
Andrew Dunstan <andrew@dunslane.net> writes: > I know we've discussed this before, but I have just had the unpleasant > experience of trying to get around the difficulty of inserting into a > foreign table with a serial field, surely a common enough scenario that > we should try to deal with it better. The solution of using a local > sequence really doesn't work, as there might be multiple users of the > table, as there will be in my scenario. I opted instead to get a value > from the foreign sequence explicitly before inserting, but that's pretty > ugly. So I am wondering (without having looked at all closely at it) if > we could set an option to tell the FDW that we want the foreign default > to be used instead of a local one. Is the difficulty that we don't know > if a value has been explicitly supplied or not? Maybe we could have some > magic value that we could use instead ('foreign_default'?). I'm just > throwing out ideas here, but this is really a wart that could well do > with attention. I'm not awake enough to recall the previous discussions of remote default-value insertion in any detail, but they were extensive, and no one has proposed solutions to the problems we hit. Please consult the archives. regards, tom lane
On 11/15/2016 10:49 AM, Tom Lane wrote: > Andrew Dunstan <andrew@dunslane.net> writes: >> I know we've discussed this before, but I have just had the unpleasant >> experience of trying to get around the difficulty of inserting into a >> foreign table with a serial field, surely a common enough scenario that >> we should try to deal with it better. The solution of using a local >> sequence really doesn't work, as there might be multiple users of the >> table, as there will be in my scenario. I opted instead to get a value >> from the foreign sequence explicitly before inserting, but that's pretty >> ugly. So I am wondering (without having looked at all closely at it) if >> we could set an option to tell the FDW that we want the foreign default >> to be used instead of a local one. Is the difficulty that we don't know >> if a value has been explicitly supplied or not? Maybe we could have some >> magic value that we could use instead ('foreign_default'?). I'm just >> throwing out ideas here, but this is really a wart that could well do >> with attention. > I'm not awake enough to recall the previous discussions of remote > default-value insertion in any detail, but they were extensive, and > no one has proposed solutions to the problems we hit. Please consult > the archives. I will look back further, But I see in 2013 Stephen said this: > At first blush, with 'simple' writable views, perhaps that can just be a > view definition on the remote side which doesn't include that column and > therefore that column won't be sent to the remote side explicitly but, > but the view, running on the remote, would turn around and pick up the > default value for any fields which aren't in the view definition when > inserting into the table underneath. and you replied > Yeah, I think the possibility of such a workaround was one of the > reasons we decided it was okay to support only locally-computed > defaults for now. The trouble in my case is I actually need to know the serial column value, so using a view that hides it doesn't work. cheers andrew