On Tue, Sep 26, 2017 at 4:57 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
I tried to address only the ALTER TABLE ... ADD COLUMN IF NOT EXISTS statement, and do not touch CREATE TABLE statements...
And since our docs don't explain the equivalence in terms of ALTER TABLE we are not falsifying anything.
For example when we add a new SERIAL column to a relation:
ALTER TABLE foo ADD COLUMN bar SERIAL;
What I understood is actually PostgreSQL will convert it to:
1. CREATE SEQUENCE foo_bar_seq;
2. ALTER TABLE foo ADD COLUMN bar INTEGER DEFAULT nextval('foo_bar_seq');
3. ALTER SEQUENCE foo_bar_seq OWNER BY foo.bar;
[...]
> I don't feel like this is exactly a "must fix" problem, and it certainly > isn't one that we should fix by introducing different oddities of > behavior. >
When I see the code I felt the same... :-(
Agreed, but it seems worthwhile to consider making it work as the OP expected.
This patch doesn't falsifies that, because will act just when IF NOT EXISTS is used...
And we already deviate for ALTER TABLE by not strictly adhering to the specified format: tablename_colname_seq; instead we allow for appending "N" to the end of the name if necessary to make the sequence name unique.
It seems like we'd want to invoke:
CREATE SEQUENCE IF NOT EXISTS tablename_colname_seq
If the corresponding add column is likewise IF NOT EXISTS.
If we detect the column was newly created maybe then also issue a RESET SEQUENCE just in case we picked up a left-over? This feels both cleaner and more dangerous than just inspecting everything first and deciding how to proceed on both fronts.