Nested Schemata, in a Standard-Compliant Way?
Greetings. For some people the "what?" and "why?" of this will be immediately obvious from the title, but I'm going to spend a little time on those before "whether?" and "how?" We have schemata. They're namespaces; very convenient for organizing things. They let you group tables and other entities together, and, by setting search_path, only see the ones which presently interest you. In fact, they're pretty similar to directories in a filesystem... except that they don't nest. Imagine a filesystem where you could have directories, but the directories could only contain files, not other directories. (Like the first Unix on the PDP-7, or DOS before 2.0.) You could, of course, use your own delimiters. And we do; often along the lines of: schema.category_subcategory_table. You can't really use these to establish context, however. The system doesn't recognize category_subcategory as a "place". So you can't easily deal with a subset of your tables, and the combination of many tables and long names tends to be messy. So, for example, I'd like to be able to say something like this: SELECT * FROM /projects/contacts/people; Or: cd /projects/contacts; SELECT * FROM people; We use / for division, so that probably isn't plausible, but it makes for a familiar example. I'm wondering whether such a feature could be added, without breaking either existing code, or compliance with the SQL standard. For instance, borrowing :: from languages like Ruby and Perl: SELECT * FROM ::projects::contacts::people; -- Absolute path cd ::projects; -- Session-specific SELECT * FROM contacts::people; -- Relative path I'm not necessarily saying this is the best delimiter, but the colon isn't valid in unquoted identifiers, so it's probably a choice which would have minimal impact. Now, you could do a fair job of this just within the client, but my thought is that this would be better if actually supported by the database. For instance, having representation in the system tables. So, then: can it be done? Should it be done? I can say easily that my database life would be better for having this, but there do tend to be those nasty lurking problems which aren't obvious. -- Ray Brinzer
Re: Nested Schemata, in a Standard-Compliant Way?
Raymond Brinzer writes: > So, for example, I'd like to be able to say something like this: > SELECT * FROM /projects/contacts/people; I looked into this many years ago. (The reason why pg_namespace is called that and not pg_schema is exactly that I thought it might someday include sub-schemas.) I don't think it's possible to do it without huge ambiguity problems, unless you introduce some separator other than dot, as indeed you suggest here. But I also don't think that using some other separator is a good idea. There's not that much free punctuation available (unless you want to step outside core ASCII, which'd bring its own problems). Pretty much every character that isn't otherwise nailed down is allowed as an operator character, meaning that redefining it is very likely to break somebody's application or extension. We had huge problems even with taking over the => digraph, never mind single characters. In the end the functionality-versus-problems ratio is just not going to be very good. regards, tom lane
Re: Nested Schemata, in a Standard-Compliant Way?
Hello Ray, On Tue, 2021-09-28 at 09:24 -0400, Raymond Brinzer wrote: > Greetings. > > > > I'm wondering whether such a feature could be added, without breaking > either existing code, or compliance with the SQL standard. For > instance, borrowing :: from languages like Ruby and Perl: > > SELECT * FROM ::projects::contacts::people; -- Absolute path > cd ::projects; -- Session-specific > SELECT * FROM contacts::people; -- Relative path > Double colons are used for casting. E.g., $1::INET or $1::INTEGER where $1 is a string. What you are after are sub schemas. set schema projects; set subschema contacts; select * from people; I don't know enough about the catalogue tables to know if a tree structure for schemas is possible, the amount of work involved or even if there is a valid use case. My 2 cents.
Re: Nested Schemata, in a Standard-Compliant Way?
On Tue, Sep 28, 2021 at 10:13 AM rob stone wrote: > Double colons are used for casting. > E.g., $1::INET or $1::INTEGER where $1 is a string. Quite right; slipped my mind. Thank you. -- Ray Brinzer
Re: Nested Schemata, in a Standard-Compliant Way?
On Tue, Sep 28, 2021 at 9:36 AM Tom Lane wrote: > I don't think it's possible to do it without huge ambiguity > problems, unless you introduce some separator other than dot, as indeed > you suggest here. Heh... the moment I saw you'd replied, I thought, "Uh oh!"... because I think of you as "the reality guy" here. And, sure enough, you came with a bucket of cold water. :-) I haven't explored the matter thoroughly enough to give up all hope in finding a solution which offers a decent ratio. In the end, though, it wouldn't surprise me at all if you were right. Single characters are too dear. Digraphs, maybe. Trigraphs? I know it's getting ugly, but it still might be a net reduction in ugliness for some people, which could be ignored by most. > (The reason why pg_namespace is called > that and not pg_schema is exactly that I > thought it might someday include sub-schemas.) I'd noticed the name; it's encouraging that at least people think it *would be* a good idea. -- Ray Brinzer
Problem with identity column & related sequences
I am using postgresql-12.8. I am using I am making use of an identity column for part of a scripts to process some updated data. Because of the way the script is called I don't necessarily know if this column is going to exist in the table I am working on so I have a step that will conditionally create the column if it doesn't already exist, i.e. alter table mytable add column if not exists unique_id integer generated always as identity; This works great if the unique_id column doesn't exist. If the column does exist, I get the notice NOTICE: column "unique_id" of relation "mytable" already exists, skipping ALTER TABLE As far as the messages are concerned everything worked as expected. The problem is that even though the column already exists it skipped the first part of the command it and seems to have followed through at least a portion of the second part and created a second sequence to handle the generated identity value even though an existing sequence already exists for the existing column. Then when I try to update the table I end up getting an error ERROR: more than one owned sequence found which I guess makes sense based on what happened but it seems like the "if not exists" should short circuit the whole thing and result in nothing changing. Now I'm stuck & I have to effectively drop the column and re-add the column. I found some references to other "more than one owned sequence" issues from a couple of years back but this seems to be a different issue. My question is whether this is the expected behavior and if so is there another way to get what I want from a similar command (or commands)? Right now I'm going through a rather clunky plpgsql function to check if the column exists instead of relying on the "if not exists logic". -- Jeff Hoffmann PropertyKey
Re: Problem with identity column & related sequences
Jeff Hoffmann writes: > I am using postgresql-12.8. I am using I am making use of an identity > column for part of a scripts to process some updated data. Because of > the way the script is called I don't necessarily know if this column > is going to exist in the table I am working on so I have a step that > will conditionally create the column if it doesn't already exist, i.e. > alter table mytable add column if not exists unique_id integer > generated always as identity; You're right, this sort of thing does not work very well in v12 and before. We fixed it in v13, but the changes seemed far too invasive to risk a back-patch [1]. regards, tom lane [1] https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=1281a5c90
Re: Problem with identity column & related sequences
Thanks. I was sort of expecting that answer but I didn't see where it was addressed specifically. Unfortunately I'm stuck on v12 for the time being so I guess it's back to the workaround. On Tue, Sep 28, 2021 at 2:13 PM Tom Lane wrote: > > Jeff Hoffmann writes: > > I am using postgresql-12.8. I am using I am making use of an identity > > column for part of a scripts to process some updated data. Because of > > the way the script is called I don't necessarily know if this column > > is going to exist in the table I am working on so I have a step that > > will conditionally create the column if it doesn't already exist, i.e. > > > alter table mytable add column if not exists unique_id integer > > generated always as identity; > > You're right, this sort of thing does not work very well in v12 and > before. We fixed it in v13, but the changes seemed far too invasive > to risk a back-patch [1]. > > regards, tom lane > > [1] > https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=1281a5c90 -- Jeff Hoffmann Head Plate Spinner PropertyKey Office: 612-800-6444 j...@propertykey.com
Re: statement_timeout vs DECLARE CURSOR
I wrote: > Christophe Pettus writes: >> A bit more poking revealed the reason: The ON HOLD cursor's query is >> executed at commit time (which is, logically, not interruptible), but that's >> all wrapped in the single statement outside of a transaction. > Hmm ... seems like a bit of a UX failure. I wonder why we don't persist > such cursors before we get into the uninterruptible part of COMMIT. Oh, I see the issue. It's not that that part of COMMIT isn't interruptible; you can control-C out of it just fine. The problem is that finish_xact_command() disarms the statement timeout before starting CommitTransactionCommand at all. We could imagine pushing the responsibility for that down into xact.c, allowing it to happen after CommitTransaction has finished running user-defined code. But it seems like a bit of a mess because there are so many other code paths there. Not sure how to avoid future bugs-of-omission. regards, tom lane
