Re: [HACKERS] pg_constraint
For tracking of Foreign Keys, Check constraints, and maybe NULL / NOT NULL (specific type of check constraint) I intend to create (as per suggestion) pg_constraint. Hmmm...I don't see the need at all for NOT NULL constraint tracking. The spec doesn't seem to require it and we do not have names for them anyway. Even if they were given names, it'd be pointless, as there's only one per column. Primary keys and unique keys are SQL constraints - are you going to bother tracking them as well or leave them in the current format? Maybe you could do it with a view or something. Why not just create a pg_references table and leave pg_relcheck as is? Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Vote totals for SET in aborted transaction
Marc G. Fournier writes: Myself, I wonder why Oracle went the route they went ... does anyone have access to a Sybase / Informix system, to confirm how they do it? Is Oracle the 'odd man out', or are we going to be that? *Adding* something (ie. DROP TABLE rollbacks) that nobody appears to have is one thing ... but changing the behaviour is a totally different .. FWIW, Ingres also doesn't rollback SET. However all its SET functionality is the sort of stuff you wouldn't assume to rollback: auto-commit connection journaling logging session work locations maxidle You cannot do something sane like modify the date output through SET. Lee. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] WAL - Replication
On Fri, 2002-04-26 at 07:38, Curt Sampson wrote: On Thu, 25 Apr 2002, Bruce Momjian wrote: WAL files are kept only until an fsync(), checkpoint, then reused. One could keep them longer though, if one really wanted to. Also, the info is tied to direct locations in the file. You could do this for hot backup, but it would require quite bit of coding to make it work. That's kind of too bad, since log shipping is a very popular method of backup and replication. Now again from my just aquired DB2 knowledge: DB2 can run in two modes 1) similar to ours, where logs are reused after checkpoints/commits allow it. 2) with log archiving: logs are never reused, but when system determines it no longer needs them, it will hand said log over to archiving process that will archive it (usually do a backup to some other place and then delete it). This mode is used when online backup and restore functionality is desired. This is something that could be interesting for 24x7 reliability. - Hannu ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] pg_constraint
For tracking of Foreign Keys, Check constraints, and maybe NULL / NOT NULL (specific type of check constraint) I intend to create (as per suggestion) pg_constraint. Hmmm...I don't see the need at all for NOT NULL constraint tracking. The spec doesn't seem to require it and we do not have names for them anyway. Even if they were given names, it'd be pointless, as there's only one per column. Correct me if I'm wrong, but aren't NOT NULL constraints a shortform of the similar CHECK constraint (according to spec which I don't have infront of me). I've been debating combining the 2 and allowing names on them, but won't do this yet. CHECK (VALUE NOT NULL) would mark the pg_attribute column and assign the name. Primary keys and unique keys are SQL constraints - are you going to bother tracking them as well or leave them in the current format? Maybe you could do it with a view or something. Why not just create a pg_references table and leave pg_relcheck as is? relcheck needs changes anyway. It needs to track the specific columns that it depends on, rather than simply the table. This is for reasons of DROP COLUMN. Last thing you want is a bad check constraint after that ;) The other reason is that they're supposed to be in the same namespace (which makes sense) and having each constraint in its own table would be silly. Of note, the above table should also have immediate, and deferrable bools attached to it. I debated about the primary / unique keys, but indicies seem to do a good enough job with those. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Block size: 8K or 16K?
Bruce Momjian wrote: Curt Sampson wrote: On Thu, 25 Apr 2002, mlw wrote: ...but my gut tells me that using 16K blocks will increase performance over 8K. Aleady I have seen a sequential scan of a large table go from 20 seconds using 8K to 17.3 seconds using 16K. You should be able to get the same performance increase with 8K blocks by reading two blocks at a time while doing sequential scans. That's why I've been promoting this idea of changing postgres to do its own read-ahead. Of course, Bruce might be right that the OS read-ahead may take care of this anyway, but then why would switching to 16K blocks improve sequential scans? Possibly because I'm missing something here. I am almost sure that increasing the block size or doing read-ahead in the db will only improve performance if someone is performing seeks in the file at the same time, and hence OS readahead is being turned off. I largely agree with you, however, don't underestimate the overhead of a read() call. By doubling the block size, the overhead of my full table scan was cut in half, thus potentially more efficient, 20 seconds was reduced to 17. (That was on a machine only doing one query, not one under full load, so the real effect may be much more subtle.) In fact, I posted some results of a comparison between 16k and 8k blocks, I saw very little difference on most tests while a couple looked pretty interesting. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] PSQL \x \l command issues
I just recently upgraded from 7.0.x to 7.2.1. I installed from
postgresql-7.2.1-2PGDG.i386.rpm on a Linux Redhat 7.1 system. I was
able to resolve most dependancies, except for it telling me that I
needed libreadline.so.4, which ldconfig -p|grep readline showed me I
already had, so forced a --nodeps on it.
Here's a self explanitory paste of what happens when I use \x or \l in
PSQL
-
psql --version
psql (PostgreSQL) 7.2.1
contains support for: readline, history, multibyte
Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group
Portions Copyright (c) 1996, Regents of the University of California
Read the file COPYRIGHT or use the command \copyright to see the
usage and distribution terms.
psql -E template1
* QUERY **
SELECT usesuper FROM pg_user WHERE usename = 'root'
**
Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
template1=# \z
* QUERY **
SELECT relname as Table,
relacl as Access privileges
FROM pg_class
WHERE relkind in ('r', 'v', 'S') AND
relname NOT LIKE 'pg$_%' ESCAPE '$'
ORDER BY 1;
**
ERROR: parser: parse error at or near escape
template1=# \l
* QUERY **
SELECT d.datname as Name,
u.usename as Owner,
pg_encoding_to_char(d.encoding) as Encoding
FROM pg_database d LEFT JOIN pg_user u ON d.datdba = u.usesysid
ORDER BY 1;
**
ERROR: OUTER JOIN is not yet supported
template1=# \q
As you can see, \x and \l in PSQL fail to work straight from
installation in my case. Anybody have any ideas?
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] Vote totals for SET in aborted transaction
Curt Sampson wrote: On Fri, 26 Apr 2002, Marc G. Fournier wrote: NOTE that I *do* think that #1 is what *should* happen, but there should be some way of turning off that behaviour so that we don't screw up ppl expecting Oracles behaviour ... I don't think this follows. If it's only for people's expectations, but we default to #1, their expectations will be violated until they figure out that the option is there. After they figure out it's there, well, they don't expect it to behave like Oracle any more, so they don't need the switch, right? Beeing able to read is definitely an advantage in the IT world. Someone just has to do it before finishing the implementation based on assumptions :-) Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] multibyte support is now enabled by default
I have enabled the multibyte support by default. The default encoding is SQL_ASCII. Note that I just modify configure minimu, and I will remove unnecessary staffs including #ifdef MULTIBYTE step by step... -- Tatsuo Ishii ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Vote totals for SET in aborted transaction
Bruce Momjian wrote: Marc G. Fournier wrote: On Thu, 25 Apr 2002, Bruce Momjian wrote: Marc is suggesting we may want to match Oracle somehow. I just want to have our SET work on a sane manner. Myself, I wonder why Oracle went the route they went ... does anyone have access to a Sybase / Informix system, to confirm how they do it? Is Oracle the 'odd man out', or are we going to be that? *Adding* something (ie. DROP TABLE rollbacks) that nobody appears to have is one thing ... but changing the behaviour is a totally different ... Yes, let's find out what the others do. I don't see DROP TABLE rollbacking as totally different. How is it different from SET? Man, you should know that our transactions are truly all or nothing. If you discard a transaction, the stamps xmin and xmax are ignored. This is a fundamental feature of Postgres, and if you're half through a utility command when you ERROR out, it guarantees consistency of the catalog. And now you want us to violate this concept for compatibility to Oracle's misbehaviour? No, thanks! Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Vote totals for SET in aborted transaction
On Fri, 26 Apr 2002, Jan Wieck wrote: Bruce Momjian wrote: Marc G. Fournier wrote: On Thu, 25 Apr 2002, Bruce Momjian wrote: Marc is suggesting we may want to match Oracle somehow. I just want to have our SET work on a sane manner. Myself, I wonder why Oracle went the route they went ... does anyone have access to a Sybase / Informix system, to confirm how they do it? Is Oracle the 'odd man out', or are we going to be that? *Adding* something (ie. DROP TABLE rollbacks) that nobody appears to have is one thing ... but changing the behaviour is a totally different ... Yes, let's find out what the others do. I don't see DROP TABLE rollbacking as totally different. How is it different from SET? Man, you should know that our transactions are truly all or nothing. If you discard a transaction, the stamps xmin and xmax are ignored. This is a fundamental feature of Postgres, and if you're half through a utility command when you ERROR out, it guarantees consistency of the catalog. And now you want us to violate this concept for compatibility to Oracle's misbehaviour? No, thanks! How does SET relate to xmin/xmax? :) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] PSQL \x \l command issues
Shad [EMAIL PROTECTED] writes: I just recently upgraded from 7.0.x to 7.2.1. I installed from postgresql-7.2.1-2PGDG.i386.rpm on a Linux Redhat 7.1 system. I was able to resolve most dependancies, except for it telling me that I needed libreadline.so.4, which ldconfig -p|grep readline showed me I already had, so forced a --nodeps on it. Here's a self explanitory paste of what happens when I use \x or \l in PSQL It looks like you may still have some of the old installation around--what does select version(); tell you? -Doug ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] pg_constraint
Rod Taylor [EMAIL PROTECTED] writes:
For tracking of Foreign Keys, Check constraints, and maybe NULL / NOT
NULL (specific type of check constraint) I intend to create (as per
suggestion) pg_constraint.
conrelid
conname
contype ('c'heck, 'f'oreign key, ???)
'u'unique, 'p'rimary key, 'n'ot null seem to cover it
conkey (int2vector of columns of relid, like pg_index.indkey)
connum int4 -- unique identifying constraint number for the relation
id.
consrc
conbin
Dependencies would be on conrelid, and connum in pg_depend. If each
constraint has a unique number for the relation OIDs aren't required
here. Much like pg_attribute.
Could we instead insist on a unique name per-table, and make this table's
key be (conrelid, conname)? Assigning a number seems quite artificial.
consrc/conbin seem to only cover the check-constraint case. Need some
thought about what to store for foreign keys (ideally, enough info for
pg_dump to reconstruct the REFERENCES spec without looking at the
triggers) and unique/primary keys (a link to the implementing index
seems like a good idea here).
I'm not exactly sure how to find out what columns a check constraint
depends on, but I'm sure I'll figure that out sooner or later.
pull_var_clause() on the nodetree representation is your friend.
I see a difficulty in the above representation though: what if a check
constraint refers to INDEX_MAX_KEY columns? Maybe conkey had better
be an int2[] variable-length array.
Any thoughts or suggestions? Is there any reason to allow a check in
a namespace other than the relation it's tied to? Spec seems to allow
that, but is it actually useful?
For constraints tied to tables, namespaces are irrelevant.
There is something in the spec about stand-alone assertions that can
specify cross-table constraints, but I think that's a task for some
future year.
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] pg_constraint
Could we instead insist on a unique name per-table, and make this table's key be (conrelid, conname)? Assigning a number seems quite artificial. The only problem with this is that I don't want the rename of a constraint to have to fall over into the pg_depend table. pg_depend is currently happy with system OIDS or a Relation OID and some unique number to represent it -- much as pg_description wouldn't want to know the name of the constraint for the ability to add a comment to it. consrc/conbin seem to only cover the check-constraint case. Need some thought about what to store for foreign keys (ideally, enough info for pg_dump to reconstruct the REFERENCES spec without looking at the triggers) and unique/primary keys (a link to the implementing index seems like a good idea here). I will implement the various flags required for these. conupdtyp, condeltyp (on update type and on delete type respectively) as well as immediate and deferrable bools. I'm not exactly sure how to find out what columns a check constraint depends on, but I'm sure I'll figure that out sooner or later. pull_var_clause() on the nodetree representation is your friend. Thanks for the tip. I see a difficulty in the above representation though: what if a check constraint refers to INDEX_MAX_KEY columns? Maybe conkey had better be an int2[] variable-length array. Good point. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] PSQL \x \l command issues
Shad [EMAIL PROTECTED] writes: I just recently upgraded from 7.0.x to 7.2.1. You are clearly still talking to the 7.0 server: ERROR: OUTER JOIN is not yet supported In general, psql's backslash commands tend to be version-specific, and may fail when talking to a server of a different version. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] syslog support by default
Tatsuo Ishii [EMAIL PROTECTED] writes: I have removed the --enable-syslog option. Now as far as the system has syslog(), the syslog support code is always in the build. If this seems ok, I will update the doc. Seems reasonable. It might be a good idea for configure to verify that the syslog.h header is present, as well as the syslog() library routine, before enabling HAVE_SYSLOG. Done. -- Tatsuo Ishii ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Vote totals for SET in aborted transaction
Lincoln Yeoh [EMAIL PROTECTED] writes: Coz some things should not be rolled back. So you guys might come up with a different keyword for it. CONFIG: for non transactional stuff that can appear as SQL statements. SET: for stuff that can be transactional. People keep suggesting this, and I keep asking for a concrete example where non-rollback is needed, and I keep not getting one. I can't see the value of investing work in creating an alternative behavior when we have no solid example to justify it. The Oracle compatibility argument would have some weight if we were making any concerted effort to be Oracle-compatible across the board; but I have not detected any enthusiasm for that. Given that it's not even the same syntax (SET ... vs ALTER SESSION ...) I'm not sure why an Oracle user would expect it to behave exactly the same. Practical example: Does doing an enable seqscan affect OTHER db connections and transactions as well? There are no SET commands that affect other backends. (There are GUC variables with system-wide effects, but we don't allow them to be changed by SET; rollback or not won't affect that.) regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] WAL - Replication
Hannu Krosing [EMAIL PROTECTED] writes: DB2 can run in two modes 1) similar to ours, where logs are reused after checkpoints/commits allow it. 2) with log archiving: logs are never reused, but when system determines it no longer needs them, it will hand said log over to archiving process that will archive it (usually do a backup to some other place and then delete it). There is in fact the skeleton of support in xlog.c for passing unwanted log segments over to an archiver, rather than recycling them. So far no one's done anything with the facility. I think the main problem is the one Bruce cited: because the WAL representation is tied to physical tuple locations and so forth, it's only useful to a slave that has an *exact* duplicate of the master's entire database cluster. That's not useless, but it's pretty restrictive. It could be useful for incremental backup, though I'm not sure how efficient it is for the purpose. WAL logs tend to be pretty voluminous. At the very least you'd probably want enough smarts in the archiver to strip out the page-image records. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Vote totals for SET in aborted transaction
Jan Wieck wrote: Bruce Momjian wrote: Marc G. Fournier wrote: On Thu, 25 Apr 2002, Bruce Momjian wrote: Marc is suggesting we may want to match Oracle somehow. I just want to have our SET work on a sane manner. Myself, I wonder why Oracle went the route they went ... does anyone have access to a Sybase / Informix system, to confirm how they do it? Is Oracle the 'odd man out', or are we going to be that? *Adding* something (ie. DROP TABLE rollbacks) that nobody appears to have is one thing ... but changing the behaviour is a totally different ... Yes, let's find out what the others do. I don't see DROP TABLE rollbacking as totally different. How is it different from SET? Man, you should know that our transactions are truly all or nothing. If you discard a transaction, the stamps xmin and xmax are ignored. This is a fundamental feature of Postgres, and if you're half through a utility command when you ERROR out, it guarantees consistency of the catalog. And now you want us to violate this concept for compatibility to Oracle's misbehaviour? No, thanks! So you do see a difference between SET and DROP TABLE because the second is a utility command. OK, I'll buy that, but my point was different. My point was that we don't match Oracle for DROP TABLE, so why is matching for SET so important? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Vote totals for SET in aborted transaction
Tom Lane wrote: Lincoln Yeoh [EMAIL PROTECTED] writes: Coz some things should not be rolled back. So you guys might come up with a different keyword for it. CONFIG: for non transactional stuff that can appear as SQL statements. SET: for stuff that can be transactional. People keep suggesting this, and I keep asking for a concrete example where non-rollback is needed, and I keep not getting one. I can't see the value of investing work in creating an alternative behavior when we have no solid example to justify it. The Oracle compatibility argument would have some weight if we were making any concerted effort to be Oracle-compatible across the board; but I have not detected any enthusiasm for that. Given that it's not even the same syntax (SET ... vs ALTER SESSION ...) I'm not sure why an Oracle user would expect it to behave exactly the same. Agreed. OK, let me summarize. We had a vote that was overwhemingly #1. Marc made a good point that we should see how other databases behave, and we now know that Oracle and Ingres do #3 (honor all SETs in an aborted transaction). Does anyone want to change their vote from #1 to #3. Second, there is the idea of doing #1, and having a GUC variable for #3. Does anyone want that? I think Marc may. Anyone else? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_constraint
Christopher Kings-Lynne [EMAIL PROTECTED] writes: Hmmm...I don't see the need at all for NOT NULL constraint tracking. The spec doesn't seem to require it and we do not have names for them anyway. Even if they were given names, it'd be pointless, as there's only one per column. Hmm, you're probably right. Way back when, I was thinking of naming them as a route to allowing DROP CONSTRAINT for them --- but given the ALTER TABLE SET/DROP NOT NULL syntax that we have now, supporting DROP CONSTRAINT is not really necessary. So I concur that not-null isn't a feature that pg_constraint needs to deal with. Why not just create a pg_references table and leave pg_relcheck as is? One reason is that that structure wouldn't guarantee that check-constraint names are distinct from references/unique-constraint names, which'd make life difficult for DROP CONSTRAINT. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Vote totals for SET in aborted transaction
Jan Wieck [EMAIL PROTECTED] writes: SET does not. But Bruce said he doesn't see DROP TABLE beeing totally different. That is related to xmin/xmax, isn't it? I think what Bruce meant was if rollback is good for DROP TABLE, why isn't it good for SET? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Vote totals for SET in aborted transaction
At 10:34 AM 4/26/02 -0400, Tom Lane wrote: Lincoln Yeoh [EMAIL PROTECTED] writes: Coz some things should not be rolled back. So you guys might come up with a different keyword for it. CONFIG: for non transactional stuff that can appear as SQL statements. SET: for stuff that can be transactional. People keep suggesting this, and I keep asking for a concrete example where non-rollback is needed, and I keep not getting one. I can't see Sorry, I wasn't clear enough. I'm not asking for non-rollback behaviour. I was trying to say that _IF_ one ever needs to SET stuff that can't be rolled back then it may be better to use some other keyword for that feature. I'm actually for #1 SET being rolled back and to not have any Oracle behaviour settings at all. Anything that can't be rolled back shouldn't use SET. Practical example: Does doing an enable seqscan affect OTHER db connections and transactions as well? There are no SET commands that affect other backends. (There are GUC variables with system-wide effects, but we don't allow them to be changed by SET; rollback or not won't affect that.) OK. Cheerio, Link ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Vote totals for SET in aborted transaction
Bruce Momjian wrote: So you do see a difference between SET and DROP TABLE because the second is a utility command. OK, I'll buy that, but my point was different. My point was that we don't match Oracle for DROP TABLE, so why is matching for SET so important? Good point, I never understood the compatibility issue on this level either. Applications that create/drop tables at runtime are IMNSVHO self-modifying code. Thus, I don't consider it a big porting issue. Applications that do it should be replaced, not ported. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] pg_constraint
Rod Taylor [EMAIL PROTECTED] writes: Could we instead insist on a unique name per-table, and make this table's key be (conrelid, conname)? Assigning a number seems quite artificial. The only problem with this is that I don't want the rename of a constraint to have to fall over into the pg_depend table. pg_depend is currently happy with system OIDS or a Relation OID and some unique number to represent it -- much as pg_description wouldn't want to know the name of the constraint for the ability to add a comment to it. Good points, but I think those argue for assigning OIDs to constraints after all. If that is what you want connum for then I have a *big* problem with it: aren't you assuming that connum will be distinct from any attribute number that the relation might have? What's going to enforce that? Besides, the approach doesn't scale to allow other kinds of objects associated with a relation (just try keeping attnum, connum, foonum, and barnum from overlapping...). I had once thought that we could avoid assigning OIDs to rules and triggers, but learned differently as I got into the implementation. I'm thinking that constraints will be the same kind of thing; it'll be a lot easier if you give them OIDs. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Vote totals for SET in aborted transaction
On Fri, 26 Apr 2002, Bruce Momjian wrote: Tom Lane wrote: Lincoln Yeoh [EMAIL PROTECTED] writes: Coz some things should not be rolled back. So you guys might come up with a different keyword for it. CONFIG: for non transactional stuff that can appear as SQL statements. SET: for stuff that can be transactional. People keep suggesting this, and I keep asking for a concrete example where non-rollback is needed, and I keep not getting one. I can't see the value of investing work in creating an alternative behavior when we have no solid example to justify it. The Oracle compatibility argument would have some weight if we were making any concerted effort to be Oracle-compatible across the board; but I have not detected any enthusiasm for that. Given that it's not even the same syntax (SET ... vs ALTER SESSION ...) I'm not sure why an Oracle user would expect it to behave exactly the same. Agreed. OK, let me summarize. We had a vote that was overwhemingly #1. Marc made a good point that we should see how other databases behave, and we now know that Oracle and Ingres do #3 (honor all SETs in an aborted transaction). Does anyone want to change their vote from #1 to #3. Second, there is the idea of doing #1, and having a GUC variable for #3. Does anyone want that? I think Marc may. Anyone else? Actually, in light of Tom's comment about it not being the same syntax, I have to admit that I missed that syntax difference in the original post :( I withdraw my GUC variable desire, unless/until someone does go with an 'ALTER SESSION' command ... ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Vote totals for SET in aborted transaction
Marc G. Fournier wrote: Second, there is the idea of doing #1, and having a GUC variable for #3. Does anyone want that? I think Marc may. Anyone else? Actually, in light of Tom's comment about it not being the same syntax, I have to admit that I missed that syntax difference in the original post :( I withdraw my GUC variable desire, unless/until someone does go with an 'ALTER SESSION' command ... It is good we had the 'compatibility' discussion. It is an important point to always consider. TODO updated: o Abort all SET changes made in an aborted transaction -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] WAL - Replication
Tom Lane wrote: Hannu Krosing [EMAIL PROTECTED] writes: DB2 can run in two modes 1) similar to ours, where logs are reused after checkpoints/commits allow it. 2) with log archiving: logs are never reused, but when system determines it no longer needs them, it will hand said log over to archiving process that will archive it (usually do a backup to some other place and then delete it). There is in fact the skeleton of support in xlog.c for passing unwanted log segments over to an archiver, rather than recycling them. So far no one's done anything with the facility. I think the main problem is the one Bruce cited: because the WAL representation is tied to physical tuple locations and so forth, it's only useful to a slave that has an *exact* duplicate of the master's entire database cluster. That's not useless, but it's pretty restrictive. It could be useful for incremental backup, though I'm not sure how efficient it is for the purpose. WAL logs tend to be pretty voluminous. At the very least you'd probably want enough smarts in the archiver to strip out the page-image records. Yes, I think the bottom line is that we would need to add some things to the WAL file to make archiving the logs work, for either point-in-time recovery, or replication, both of which we need. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Vote totals for SET in aborted transaction
At 11:49 AM 4/26/02 -0400, Tom Lane wrote: I'm still looking for an example of something that is (a) reasonable to set on a per-backend basis, and (b) not reasonable to roll back if it's set in a transaction that fails. The way I see it is if (a) and you don't want it rolled back, you could put it in a transaction of its own. BEGIN; SET backend pref; COMMIT; And if that transaction fails, maybe it should :). So other than for performance, the example should also have a reason to belong with other statements in a transaction. Have a nice weekend, Link. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Vote totals for SET in aborted transaction
At 11:50 25/04/02 -0400, Bruce Momjian wrote: Marc G. Fournier wrote: Just curious here, but has anyone taken the time to see how others are doing this? For instance, if we go with 1, are going against how everyone else handles it? IMHO, its not a popularity contest ... Dec/RDB (and I think Oracle as well) ignores transactions. Even configuration commands (eg. setting date formats etc) ignore transactions. I think the key thing here is that they view variables as part of a programming language built on top of the database backend (like plpgsql). As a result they separate variable management from database management. FWIW, I would be in the '?' camp - assuming that means some kind of session-specific setting...failing that, I'd probably start looking for an interactive form of plpgsql, so I could get persistant variables. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Arrays in system tables
Just exactly how does one get an array into a system table? Of course, _int2 and int2[] aren't normal C constructs so using it within CATALOG won't work. I suppose thats why the vector types were invented? -- Rod ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS]
Hi all, I have a problem with metadata.just read this ... Let us simply suppose a table test with 2 fileds name (varchar(10)) and age (numeric) and there b values as name=abc,age=20. Now in a function i need to develop a list where the column header info has to b made in this format i.e., as column name, column type, column width. am getting the column name using PQfname function, column type using PQftype and column size using PQfsize... am also able to get each filed values n their size correctly.. but the problem now is ..when i use PQfsize to get the column header size then i get a -1 if the field is a variable length in nature else i get the exact sizei.e., in the above declared table for both the name and age being variable fields am getting -1 for their size...now i need some mechanism to get 10 for name header and number of bytes allocated for age(30,6)...is their any way to overcome this problem? Is this a draw back in Postgre? Is there any way i can get the exact size that i allocated when creating the table.infact most of the other databases do provide APIs with not having this problemcan anyone help me, please. Shra ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Arrays in system tables
Rod Taylor wrote: Just exactly how does one get an array into a system table? Of course, _int2 and int2[] aren't normal C constructs so using it within CATALOG won't work. I suppose thats why the vector types were invented? Well, pg_shadow had pg_class has: relacl | aclitem[] | and pg_shadow has: useconfig | text[] | so I would use those as guides. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Arrays in system tables
Ahh.. no wonder my aimless greps couldn't find anything. I should just have read the BKI stuff ;) Thanks -- Rod - Original Message - From: Bruce Momjian [EMAIL PROTECTED] To: Rod Taylor [EMAIL PROTECTED] Cc: Hackers List [EMAIL PROTECTED] Sent: Saturday, April 27, 2002 2:06 AM Subject: Re: [HACKERS] Arrays in system tables Rod Taylor wrote: Just exactly how does one get an array into a system table? Of course, _int2 and int2[] aren't normal C constructs so using it within CATALOG won't work. I suppose thats why the vector types were invented? Well, pg_shadow had pg_class has: relacl | aclitem[] | and pg_shadow has: useconfig | text[] | so I would use those as guides. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
