[HACKERS] Creating DB
Is the only way to create DB in a C code to connect to Template1 and then exec the SQL string "CREATE DATABASE databasename" ? Can I create DB without connecting to template1 ?
[HACKERS] ECPG eror ...
Dear all : help me please : I compile my c++ program to connect Postgres Sql with command line : c++ -I /usr/local/pgsql/include -L /usr/local/pgsql/lib -lecpg -lpq -g -o capek.cgi capek.cc and I ve got eror " Segmentation fault (core dumped) " could any body tell me what happen .. Zudi Iswanto ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] NOTICE: Child itemid in update-chain marked as unused...
sometimes i'm getting: NOTICE: Child itemid in update-chain marked as unused - can't continue repair_frag during a simple "vacuum", db is online. pg version 7.1, on debian linux kernel 2.4. what's the problem? thanks, valter m. _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp ---(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: Fw: [HACKERS] Translators wanted
"Serguei Mokhov" <[EMAIL PROTECTED]> writes: >> Hello Peter, >> >> There was a little typo in line 73 in the original file libpq.pot: >> >> #: fe-connect.c:713 >> #, c-format >> msgid "could not socket to non-blocking mode: %s\n" >> >> missing the word 'set' between 'not' & 'socket'... Yes. Peter noticed and fixed that typo in the fe-connect.c original a few weeks ago --- but it looks like he forgot to update de.po to match. I've committed the change to CVS. Thanks! regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] NOTICE: Child itemid in update-chain marked as unused...
"V. M." <[EMAIL PROTECTED]> writes: > sometimes i'm getting: > NOTICE: Child itemid in update-chain marked as unused - can't > continue repair_frag > during a simple "vacuum", db is online. > pg version 7.1, on debian linux kernel 2.4. > what's the problem? The source code says: /* * This means that in the middle of chain there * was tuple updated by older (than XmaxRecent) * xaction and this tuple is already deleted by * me. Actually, upper part of chain should be * removed and seems that this should be handled * in scan_heap(), but it's not implemented at the * moment and so we just stop shrinking here. */ In short, an unimplemented special case in VACUUM's logic that tries to compact out free space by moving tuples around. Most people never see this message though. There must be something unusual about the pattern of updates being done on this particular table, that you see it often. regards, tom lane ---(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
[HACKERS] OID wraparound: summary and proposal
Given Hiroshi's objections, and the likelihood of compatibility problems for existing applications, I am now thinking that it's not a good idea to turn off OID generation by default. (At least not for 7.2 --- maybe in some future release we could change the default.) Based on the discussion so far, here is an attempt to flesh out the details of what to do with OIDs for 7.2: 1. Add an optional clause "WITH OIDS" or "WITHOUT OIDS" to CREATE TABLE. The default behavior will be WITH OIDS. Note: there was some discussion of a GUC variable to control the default. I'm leaning against this, mainly because having one would mean that pg_dump *must* write WITH OIDS or WITHOUT OIDS in every CREATE TABLE; else it couldn't be sure that the database schema would be correctly reconstructed. That would create dump-script portability problems and negate some of the point of having a GUC variable in the first place. So I'm thinking a fixed default is better. Note: an alternative syntax possibility is to make it look like the "with" option clauses for functions and indexes: "WITH (oids)" or "WITH (noOids)". This is uglier today, but would start to look more attractive if we invent additional CREATE TABLE options in the future --- there'd be a place to put 'em. Comments? 2. A child table will be forced to have OIDs if any of its parents do, even if WITHOUT OIDS is specified in the child's CREATE command. This is on the theory that the OID ought to act like an inherited column. 3. For a table without OIDs, no entry will be made in pg_attribute for the OID column, so an attempt to reference the OID column will draw a "no such column" error. (An alternative is to allow OID to read as nulls, but it seemed that people preferred the error to be raised.) 4. When inserting into an OID-less table, the INSERT result string will always show 0 for the OID. 5. A "relhasoids" boolean column will be added to pg_class to signal whether a table has OIDs or not. 6. COPY out WITH OIDS will ignore the "WITH OIDS" specification if the table has no OIDs. (Alternative possibility: raise an error --- is that better?) COPY in WITH OIDS will silently drop the incoming OID values. 7. Physical tuple headers won't change. If no OIDs are assigned for a particular table, the OID field in the header will be left zero. 8. OID generation will be disabled for those system tables that don't need it --- pg_listener, pg_largeobject, and pg_attribute being some major offenders that consume lots of OIDs. 9. To continue to support COMMENT ON COLUMN when columns have no OIDs, pg_description will be modified so that its primary key is (object type, object OID, column number) --- this also solves the problem that comments break if there are duplicate OIDs in different system tables. The object type is the OID of the system catalog in which the object OID appears. The column number field will be zero for all object types except columns. For a column comment, the object type and OID fields will refer to the parent table, and column number will be nonzero. 10. pg_dump will be modified to do the appropriate things with OIDs. Are there any other application programs that need to change? We had also talked about adding an INSERT ... RETURNING feature to allow applications to eliminate their dependence on looking at the OID returned by an INSERT command. I think this is a good idea, but there are still a number of unsolved issues about how it should interact with rules. Accordingly, I'm not going to try to include it in this batch of work. Comments? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Using POSIX mutex-es
1. Just changed TAS(lock) to pthread_mutex_trylock(lock) S_LOCK(lock) to pthread_mutex_lock(lock) S_UNLOCK(lock) to pthread_mutex_unlock(lock) (and S_INIT_LOCK to share mutex-es between processes). 2. pgbench was initialized with scale 10. SUN WS 10 (512Mb), Solaris 2.6 (I'm unable to test on E4500 -:() -B 16384, wal_files 8, wal_buffers 256, checkpoint_segments 64, checkpoint_timeout 3600 50 clients x 100 transactions (after initialization DB dir was saved and before each test copyed back and vacuum-ed). 3. No difference. Mutex version maybe 0.5-1 % faster (eg: 37.264238 tps vs 37.083339 tps). So - no gain, but no performance loss "from using pthread library" (I've also run tests with 1 client), at least on Solaris. And so - looks like we can use POSIX mutex-es and conditional variables (not semaphores; man pthread_cond_wait) and should implement light lmgr, probably with priority locking. Vadim ---(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] OID wraparound: summary and proposal
On Wed, 1 Aug 2001, Tom Lane wrote: > Based on the discussion so far, here is an attempt to flesh out the > details of what to do with OIDs for 7.2: > > 1. Add an optional clause "WITH OIDS" or "WITHOUT OIDS" to CREATE TABLE. > The default behavior will be WITH OIDS. > > Note: there was some discussion of a GUC variable to control the default. > > Note: an alternative syntax possibility is to make it look like the "with" > option clauses for functions and indexes: "WITH (oids)" or "WITH (noOids)". > This is uglier today, but would start to look more attractive if we invent > additional CREATE TABLE options in the future --- there'd be a place to > put 'em. Comments? I think a fixed default and placing it in parentheses are probably good ideas. > 3. For a table without OIDs, no entry will be made in pg_attribute for > the OID column, so an attempt to reference the OID column will draw a > "no such column" error. (An alternative is to allow OID to read as nulls, > but it seemed that people preferred the error to be raised.) Okay, at least the foreign key stuff will need to change (since it does a select oid), but I don't think it ever does anything with that except check for existance, so I could probably make it select 1 as reasonable replacement. ---(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] OID wraparound: summary and proposal
Bruce Momjian <[EMAIL PROTECTED]> writes: >> For input, I see no downside to just >> ignoring the incoming OIDs. For output, I can see three reasonable >> possibilities: >> >> A. Pretend WITH OIDS wasn't mentioned. This might seem to be >> "do the right thing", but a rather strong objection is that the >> app will not get back the data it was expecting. >> >> B. Return NULLs or 0s for the OIDs column. >> >> C. Raise an error and refuse to do the copy at all. >> >> C is probably the most conservative answer. > If we fail on load, we should fail on dump. Why not fail on COPY WITH > OIDS on a non-oid table? I'm confused --- I was proposing that we *not* fail on load. What's the point of failing on load? >> How so? pg_description is broken anyway given that we don't enforce OID >> uniqueness across system catalogs. Also, in the future we could > We have a script to detect them and the oid counter it unique. In what > way do we not enforce it. In a running system, once the OID counter wraps around there's no guarantee that you won't have duplicate OIDs in different system tables. The only enforcement mechanism we have is the unique indexes, and those will only check per-table. However, that's fine --- it's as much as we need. For everything except pg_description, that is. Since pg_description currently makes an unchecked and uncheckable assumption of global uniqueness of OIDs, it's broken. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] OID wraparound: summary and proposal
Tom Lane wrote: > > Given Hiroshi's objections, and the likelihood of compatibility problems > for existing applications, I am now thinking that it's not a good idea to > turn off OID generation by default. (At least not for 7.2 --- maybe in > some future release we could change the default.) > Would OIDs be globally unique or per table ? regards, Hiroshi Inoue ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] OID wraparound: summary and proposal
Hiroshi Inoue <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> >> Given Hiroshi's objections, and the likelihood of compatibility problems >> for existing applications, I am now thinking that it's not a good idea to >> turn off OID generation by default. > Would OIDs be globally unique or per table ? Same as now: if you have a unique index on 'em, they're unique within a table; otherwise, no guarantee at all (once the system wraps around). We should document this state of affairs better, of course, but I'm not proposing to change it. The point here is just to let people suppress OIDs for tables that don't need them, and thereby postpone OID wraparound. regards, tom lane ---(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
[HACKERS] Is there a way to drop and restore an index?
I recall seeing a message by Tom Lane stating that dropping and re-creating a primary index may speed up db performance. Is there a SQL command that will do this? My current method is to use pg_dump -s to dump out the schema. Then I go through and cut out everything but the CREATE INDEX lines. Then, I have to add a DROP INDEX line before that. I run this through with the psql command line program. Is there a better way? Thanks. -Tony ---(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] Is there a way to drop and restore an index?
Just off the top of my head, Couldn't you write a little PL/PGSQL procedure which queries the system tables and builds statements to execute with the new EXECUTE command for each record returned that would drop and recreate the indexes? It would take a little work but would be generic enough to automatically reindex your entire DB. Just a thought, Mike Mascari [EMAIL PROTECTED] Tony Reina wrote: > > I recall seeing a message by Tom Lane stating that dropping and > re-creating a primary index may speed up db performance. Is there a > SQL command that will do this? > > My current method is to use pg_dump -s to dump out the schema. Then I > go through and cut out everything but the CREATE INDEX lines. Then, I > have to add a DROP INDEX line before that. I run this through with the > psql command line program. > > Is there a better way? > > Thanks. > -Tony ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] OID wraparound: summary and proposal
Tom Lane wrote: > > Hiroshi Inoue <[EMAIL PROTECTED]> writes: > > Tom Lane wrote: > >> > >> Given Hiroshi's objections, and the likelihood of compatibility problems > >> for existing applications, I am now thinking that it's not a good idea to > >> turn off OID generation by default. > > > Would OIDs be globally unique or per table ? > > Same as now: if you have a unique index on 'em, they're unique within a > table; otherwise, no guarantee at all (once the system wraps around). > OIDs per table seems more important than others. Strangely enough, I've seen no objection to optional OIDs other than mine. Probably it was my mistake to have formulated a plan on the flimsy assumption. regards, Hiroshi Inoue ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Re: OID wraparound: summary and proposal
mlw <[EMAIL PROTECTED]> writes: > how hard would it be to have an OID range on a per > table basis? The existing OID generator is a system-wide counter, and couldn't reasonably be expected to do something like that. There was some talk of (in essence) eliminating the present OID generator mechanism and giving each table its own sequence object for generating per-table OIDs. It's an interesting thought, but I'm concerned about the overhead involved. At the very least we'd need to reimplement sequence objects in a lower-overhead fashion (eg, make 'em rows in a pg_sequence table rather than free-standing almost-tables). Might be worth doing someday, but I think it's orthogonal to what I'm proposing at present. There'd still be a need to suppress OID generation on tables that don't need OIDs and might have more than 4 billion inserts during their lifetime. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Is there a way to drop and restore an index?
See REINDEX. regards, tom lane ---(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] Is there a way to drop and restore an index?
Tom Lane wrote: > See REINDEX. > Thanks. -Tony ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] pltcl - lastoid
[EMAIL PROTECTED] wrote: > > I noticed that pltcl didn't have any way to get to SPI_lastoid like plpgsql does.. I >started using pltcl a lot because I like to decide when and how my queries get >planned.. so I put one together really quick > Please note that OIDs may be optional in 7.2 though it doesn't seem a problem for you. regards, Hiroshi Inoue ---(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] OID wraparound: summary and proposal
> Bruce Momjian <[EMAIL PROTECTED]> writes: > >> For input, I see no downside to just > >> ignoring the incoming OIDs. For output, I can see three reasonable > >> possibilities: > >> > >> A. Pretend WITH OIDS wasn't mentioned. This might seem to be > >> "do the right thing", but a rather strong objection is that the > >> app will not get back the data it was expecting. > >> > >> B. Return NULLs or 0s for the OIDs column. > >> > >> C. Raise an error and refuse to do the copy at all. > >> > >> C is probably the most conservative answer. > > > If we fail on load, we should fail on dump. Why not fail on COPY WITH > > OIDS on a non-oid table? > > I'm confused --- I was proposing that we *not* fail on load. What's the > point of failing on load? I meant to say we should fail on dump _and_ load. If we don't we are throwing away the oid's they are loading because though the table has no oid column. Seems like something that should fail. > > >> How so? pg_description is broken anyway given that we don't enforce OID > >> uniqueness across system catalogs. Also, in the future we could > > > We have a script to detect them and the oid counter it unique. In what > > way do we not enforce it. > > In a running system, once the OID counter wraps around there's no > guarantee that you won't have duplicate OIDs in different system > tables. The only enforcement mechanism we have is the unique indexes, > and those will only check per-table. However, that's fine --- it's > as much as we need. For everything except pg_description, that is. > Since pg_description currently makes an unchecked and uncheckable > assumption of global uniqueness of OIDs, it's broken. If you consider random table creation failures acceptible. In oid wraparound, whether pg_description could point to two rows with the same oid is the smallest part of our problem. I think the whole idea we can run reliably with an oid wraparound is questionable. -- 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Re: [PATCHES] Allow IDENT authentication on local connections (Linuxonly)
> BTW, while digging through my mail archives I discovered that Oliver > *did* already extract his "peer" auth patch and submit it as a proposed > patch --- see the pghackers archives for 3-May-2001. At the time I > think we were concerned about portability issues, but as long as it's > appropriately autoconf'd and documented, I see no real objection to > supporting SO_PEERCRED authentication. > > I do still like Helge's API (use "ident") better than adding another > auth keyword, though. Can someone find the Solaris patch submitted a few months ago that did a similar thing? I can't seem to find it. -- 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Re: [PATCHES] Allow IDENT authentication on local connections (Linux only)
Bruce Momjian <[EMAIL PROTECTED]> writes: > Can someone find the Solaris patch submitted a few months ago that did a > similar thing? I can't seem to find it. I couldn't find one either. I found a couple of unsupported assertions that Solaris and *BSD had SO_PEERCRED, so the Linux patch might work for them. We'll find out soon enough, I suppose. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Re: OID wraparound: summary and proposal
Maybe I'm being horribly stupid here, but If the thinking is that some tables can escape having an OID, thus meaning OIDs can be controlled by table, how hard would it be to have an OID range on a per table basis? Where each table to have its own notion of an OID, then OID wrap/depletion should be minimal. Tom Lane wrote: > > Given Hiroshi's objections, and the likelihood of compatibility problems > for existing applications, I am now thinking that it's not a good idea to > turn off OID generation by default. (At least not for 7.2 --- maybe in > some future release we could change the default.) > > Based on the discussion so far, here is an attempt to flesh out the > details of what to do with OIDs for 7.2: > > 1. Add an optional clause "WITH OIDS" or "WITHOUT OIDS" to CREATE TABLE. > The default behavior will be WITH OIDS. > > Note: there was some discussion of a GUC variable to control the default. > I'm leaning against this, mainly because having one would mean that > pg_dump *must* write WITH OIDS or WITHOUT OIDS in every CREATE TABLE; > else it couldn't be sure that the database schema would be correctly > reconstructed. That would create dump-script portability problems and > negate some of the point of having a GUC variable in the first place. > So I'm thinking a fixed default is better. > > Note: an alternative syntax possibility is to make it look like the "with" > option clauses for functions and indexes: "WITH (oids)" or "WITH (noOids)". > This is uglier today, but would start to look more attractive if we invent > additional CREATE TABLE options in the future --- there'd be a place to > put 'em. Comments? > > 2. A child table will be forced to have OIDs if any of its parents do, > even if WITHOUT OIDS is specified in the child's CREATE command. This is > on the theory that the OID ought to act like an inherited column. > > 3. For a table without OIDs, no entry will be made in pg_attribute for > the OID column, so an attempt to reference the OID column will draw a > "no such column" error. (An alternative is to allow OID to read as nulls, > but it seemed that people preferred the error to be raised.) > > 4. When inserting into an OID-less table, the INSERT result string will > always show 0 for the OID. > > 5. A "relhasoids" boolean column will be added to pg_class to signal > whether a table has OIDs or not. > > 6. COPY out WITH OIDS will ignore the "WITH OIDS" specification if the > table has no OIDs. (Alternative possibility: raise an error --- is that > better?) COPY in WITH OIDS will silently drop the incoming OID values. > > 7. Physical tuple headers won't change. If no OIDs are assigned for a > particular table, the OID field in the header will be left zero. > > 8. OID generation will be disabled for those system tables that don't need > it --- pg_listener, pg_largeobject, and pg_attribute being some major > offenders that consume lots of OIDs. > > 9. To continue to support COMMENT ON COLUMN when columns have no OIDs, > pg_description will be modified so that its primary key is (object type, > object OID, column number) --- this also solves the problem that comments > break if there are duplicate OIDs in different system tables. The object > type is the OID of the system catalog in which the object OID appears. > The column number field will be zero for all object types except columns. > For a column comment, the object type and OID fields will refer to the > parent table, and column number will be nonzero. > > 10. pg_dump will be modified to do the appropriate things with OIDs. Are > there any other application programs that need to change? > > We had also talked about adding an INSERT ... RETURNING feature to allow > applications to eliminate their dependence on looking at the OID returned > by an INSERT command. I think this is a good idea, but there are still > a number of unsolved issues about how it should interact with rules. > Accordingly, I'm not going to try to include it in this batch of work. > > Comments? > > regards, tom lane > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- 5-4-3-2-1 Thunderbirds are GO! http://www.mohawksoft.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Problem with FK referential actions
Stephan Szabo wrote:
> The output from the select, should I believe be (3,1), (4,1)
> not (4,1), (4,1). I think we're violating General Rule 4 (I think
> that's it) on the referential constraint definition ("For every
> row of the referenced table, its matching rows, unique matching
> rows, and non-unique matching rows are determined immediately
> before the execution of any SQL-statement. No new matching
> rows are added during the execution of that SQL-statement.")
> because when the update cascade gets done for the 2 row, we've
> changed the (2,1) to (3,1) which then gets hit by the update
> cascade on the 3 row.
>
> I was wondering if you had any thoughts on an easy way around
> it within what we have. :)
I think you're right in that it is a bug and where the
problem is. Now to get around it isn't easy. Especially in
the deferred constraint area, it is important that the
triggers see the changes made during all commands. But for
the cascade to hit the right rows only, the scans (done with
key qualification) would have to be done with a scan command
counter equal to the original queries command counter.
The old (more buggy?) behaviour should've been this annoying
"triggered data change violation". But some folks thought
it'd be a good idea to rip out that bug. See, these are the
days when you miss the old bugs :-)
Jan
--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#== [EMAIL PROTECTED] #
_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] Accessing different databases in a cluster
Is it possible to access tables in one database from another database if they're in the same cluster? I dont seem to be able to do it; is there something I have to do or is it impossible? Ie. If I have two databases accessible from the same postmaster; one called db_one and the other called db_two. %psql -U postgres -p db_one db_one=# select * from db_two.mytable; ... or, from the other perspective; %psql -U postgres -p db_two db_two=# select * from db_one.myothertable; ... Thanks, dave ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] How to find the database name during run-time
I have a function (plpgsql) and would like it to have access to the name of the current database. Unfortunately, I dont know how to ask the question. I've look in the documentation, and I can get a list of possible databases from pg_database, but I dont know which one I'm currently in. dave ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Problem with FK referential actions
On Wed, 1 Aug 2001, Jan Wieck wrote:
> Stephan Szabo wrote:
> > The output from the select, should I believe be (3,1), (4,1)
> > not (4,1), (4,1). I think we're violating General Rule 4 (I think
> > that's it) on the referential constraint definition ("For every
> > row of the referenced table, its matching rows, unique matching
> > rows, and non-unique matching rows are determined immediately
> > before the execution of any SQL-statement. No new matching
> > rows are added during the execution of that SQL-statement.")
> > because when the update cascade gets done for the 2 row, we've
> > changed the (2,1) to (3,1) which then gets hit by the update
> > cascade on the 3 row.
> >
> > I was wondering if you had any thoughts on an easy way around
> > it within what we have. :)
>
> I think you're right in that it is a bug and where the
> problem is. Now to get around it isn't easy. Especially in
> the deferred constraint area, it is important that the
> triggers see the changes made during all commands. But for
> the cascade to hit the right rows only, the scans (done with
> key qualification) would have to be done with a scan command
> counter equal to the original queries command counter.
I was afraid you were going to say something like that (basically
travelling short periods backwards in time). :( Is this something
that already can be done or would it require new support structure?
Also, I'm unconvinced that referential actions on deferred constraints
actually defer, or at least the rows they act on don't defer (excepting
no action of course) given general rule 4, unless the statement
it's referring to is the commit, but then general rule 5 (for example)
doesn't make sense since the pk rows aren't marked for deletion during
the commit (unless I'm really missing something).
> The old (more buggy?) behaviour should've been this annoying
> "triggered data change violation". But some folks thought
> it'd be a good idea to rip out that bug. See, these are the
> days when you miss the old bugs :-)
:) Actually, I think that technically what we're doing actually
is a triggered data change violation as well, since it's within
one statement.
---(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] Accessing different databases in a cluster
On Wed, 1 Aug 2001, Dave Blasby wrote: > Is it possible to access tables in one database from another database if > they're in the same cluster? I dont seem to be able to do it; is there > something I have to do or is it impossible? No, AFAIK, this isn't currently possible. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] pltcl - lastoid
Attached is the patch you suggested, with a documentation addition. Is
this correct?
> I noticed that pltcl didn't have any way to get to SPI_lastoid like plpgsql does.. I
>started using pltcl a lot because I like to decide when and how my queries get
>planned.. so I put one together really quick
>
> Sorry I don't have the original around to make a quick diff, but its a very small
>change... I think this should be in the next release, there's no reason not to have
>it.
>
> its a function with no expected arguments, so you can use it like:
> spi_exec "INSERT INTO mytable(columns...) VALUES(values..)"
> set oid [spi_lastoid]
> spi_exec "SELECT mytable_id from mytable WHERE oid=$oid"
>
> It just didn't make sense for me to use plpgsql and pltcl, or just screw them both
>and use SPI from C
>
> these changes are for src/pl/tcl/pltcl.c
>
> /* start C code */
>
> /* forward declaration */
> static int pltcl_SPI_lastoid(ClientData cdata, Tcl_Interp *interp,
> int argc, char *argv[]);
>
> /* this needs to go in in pltcl_init_interp with the rest of 'em */
> Tcl_CreateCommand(interp, "spi_lastoid",
> pltcl_SPI_lastoid, NULL, NULL);
>
>
> /**
> * pltcl_SPI_lastoid() - return the last oid. To
> *be used after insert queries
> **/
> static int
> pltcl_SPI_lastoid(ClientData cdata, Tcl_Interp *interp,
> int argc, char *argv[])
> {
> char buf[64];
> sprintf(buf,"%d",SPI_lastoid);
> Tcl_SetResult(interp, buf, TCL_VOLATILE);
> return TCL_OK;
> }
>
> /* end C code */
>
> -bob
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
>
--
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
Index: doc/src/sgml/pltcl.sgml
===
RCS file: /home/projects/pgsql/cvsroot/pgsql/doc/src/sgml/pltcl.sgml,v
retrieving revision 2.11
diff -c -r2.11 pltcl.sgml
*** doc/src/sgml/pltcl.sgml 2001/06/09 02:19:07 2.11
--- doc/src/sgml/pltcl.sgml 2001/08/01 19:32:04
***
*** 395,400
--- 395,412
+
+spi_lastoid
+
+ spi_lastoid
+
+
+ Returns the OID of the last query if it was an INSERT.
+
+
+
+
+
spi_exec ?-count n? ?-array
name? query
?loop-body?
Index: src/pl/tcl/pltcl.c
===
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/pl/tcl/pltcl.c,v
retrieving revision 1.37
diff -c -r1.37 pltcl.c
*** src/pl/tcl/pltcl.c 2001/06/09 02:19:07 1.37
--- src/pl/tcl/pltcl.c 2001/08/01 19:32:09
***
*** 144,149
--- 144,151
int tupno, HeapTuple tuple, TupleDesc
tupdesc);
static void pltcl_build_tuple_argument(HeapTuple tuple, TupleDesc tupdesc,
Tcl_DString *retval);
+ static int pltcl_SPI_lastoid(ClientData cdata, Tcl_Interp *interp,
+ int argc, char *argv[]);
/*
* This routine is a crock, and so is everyplace that calls it. The problem
***
*** 251,257
pltcl_SPI_prepare, NULL, NULL);
Tcl_CreateCommand(interp, "spi_execp",
pltcl_SPI_execp, NULL, NULL);
!
#ifdef ENABLE_PLTCL_UNKNOWN
/
* Try to load the unknown procedure from pltcl_modules
--- 253,261
pltcl_SPI_prepare, NULL, NULL);
Tcl_CreateCommand(interp, "spi_execp",
pltcl_SPI_execp, NULL, NULL);
! Tcl_CreateCommand(interp, "spi_lastoid",
! pltcl_SPI_lastoid, NULL, NULL);
!
#ifdef ENABLE_PLTCL_UNKNOWN
/
* Try to load the unknown procedure from pltcl_modules
***
*** 2270,2275
--- 2274,2294
/
memcpy(&Warn_restart, &save_restart, sizeof(Warn_restart));
sprintf(buf, "%d", ntuples);
+ Tcl_SetResult(interp, buf, TCL_VOLATILE);
+ return TCL_OK;
+ }
+
+
+ /
Re: [HACKERS] pltcl - lastoid
Kindly format OIDs with %u not %d ... otherwise it looks reasonable... regards, tom lane ---(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] OID wraparound: summary and proposal
> Given Hiroshi's objections, and the likelihood of compatibility problems > for existing applications, I am now thinking that it's not a good idea to > turn off OID generation by default. (At least not for 7.2 --- maybe in > some future release we could change the default.) This seems good. People with oid concerns usually have 1-2 huge tables and the rest are small. > Based on the discussion so far, here is an attempt to flesh out the > details of what to do with OIDs for 7.2: > > 1. Add an optional clause "WITH OIDS" or "WITHOUT OIDS" to CREATE TABLE. > The default behavior will be WITH OIDS. Makes sense. > Note: there was some discussion of a GUC variable to control the default. > I'm leaning against this, mainly because having one would mean that > pg_dump *must* write WITH OIDS or WITHOUT OIDS in every CREATE TABLE; > else it couldn't be sure that the database schema would be correctly > reconstructed. That would create dump-script portability problems and > negate some of the point of having a GUC variable in the first place. > So I'm thinking a fixed default is better. Good point. > Note: an alternative syntax possibility is to make it look like the "with" > option clauses for functions and indexes: "WITH (oids)" or "WITH (noOids)". > This is uglier today, but would start to look more attractive if we invent > additional CREATE TABLE options in the future --- there'd be a place to > put 'em. Comments? I don't like the parens. Looks ugly and I am not used to seeing them used that way. I can imagine later using WITH NOOIDS, NOBIBBLE, BABBLE. Maybe the syntax should be WITH OID, WITH NOOID? > 2. A child table will be forced to have OIDs if any of its parents do, > even if WITHOUT OIDS is specified in the child's CREATE command. This is > on the theory that the OID ought to act like an inherited column. Good point. > 3. For a table without OIDs, no entry will be made in pg_attribute for > the OID column, so an attempt to reference the OID column will draw a > "no such column" error. (An alternative is to allow OID to read as nulls, > but it seemed that people preferred the error to be raised.) Makes sense. > 6. COPY out WITH OIDS will ignore the "WITH OIDS" specification if the > table has no OIDs. (Alternative possibility: raise an error --- is that > better?) COPY in WITH OIDS will silently drop the incoming OID values. Obviously, the case here is that COPY WITH OIDS alone on a non-oid table should throw an error, while pg_dump -o should work on a database with mixed oid/non-oid. I think the right thing would be to have pg_dump check pg_class.relhasoids and issue a proper COPY statement to match the existing table. > 7. Physical tuple headers won't change. If no OIDs are assigned for a > particular table, the OID field in the header will be left zero. > > 8. OID generation will be disabled for those system tables that don't need > it --- pg_listener, pg_largeobject, and pg_attribute being some major > offenders that consume lots of OIDs. > > 9. To continue to support COMMENT ON COLUMN when columns have no OIDs, > pg_description will be modified so that its primary key is (object type, > object OID, column number) --- this also solves the problem that comments > break if there are duplicate OIDs in different system tables. The object > type is the OID of the system catalog in which the object OID appears. > The column number field will be zero for all object types except columns. > For a column comment, the object type and OID fields will refer to the > parent table, and column number will be nonzero. Sounds like a hack. I still prefer pg_attribute to have oids. Can we have temp tables have no pg_attribute oids? A hack on a hack? -- 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] OID wraparound: summary and proposal
Bruce Momjian <[EMAIL PROTECTED]> writes: >> 6. COPY out WITH OIDS will ignore the "WITH OIDS" specification if the >> table has no OIDs. (Alternative possibility: raise an error --- is that >> better?) COPY in WITH OIDS will silently drop the incoming OID values. > Obviously, the case here is that COPY WITH OIDS alone on a non-oid table > should throw an error, while pg_dump -o should work on a database with > mixed oid/non-oid. I think the right thing would be to have pg_dump > check pg_class.relhasoids and issue a proper COPY statement to match the > existing table. pg_dump clearly will need to do that, so it isn't really going to be the issue. The question is what to do when a less-clueful app issues a COPY WITH OIDS on an OID-less table. For input, I see no downside to just ignoring the incoming OIDs. For output, I can see three reasonable possibilities: A. Pretend WITH OIDS wasn't mentioned. This might seem to be "do the right thing", but a rather strong objection is that the app will not get back the data it was expecting. B. Return NULLs or 0s for the OIDs column. C. Raise an error and refuse to do the copy at all. C is probably the most conservative answer. >> 9. To continue to support COMMENT ON COLUMN when columns have no OIDs, >> pg_description will be modified so that its primary key is (object type, >> object OID, column number) --- this also solves the problem that comments >> break if there are duplicate OIDs in different system tables. The object >> type is the OID of the system catalog in which the object OID appears. >> The column number field will be zero for all object types except columns. >> For a column comment, the object type and OID fields will refer to the >> parent table, and column number will be nonzero. > Sounds like a hack. How so? pg_description is broken anyway given that we don't enforce OID uniqueness across system catalogs. Also, in the future we could consider overloading the column to have meanings for other object types. I could imagine using it to attach documentation to each of the input arguments of a function, for example. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] Accessing different databases in a cluster
At 12:22 PM 8/1/01 -0700, Stephan Szabo wrote: >On Wed, 1 Aug 2001, Dave Blasby wrote: > > > Is it possible to access tables in one database from another database if > > they're in the same cluster? I dont seem to be able to do it; is there > > something I have to do or is it impossible? > >No, AFAIK, this isn't currently possible. Really, you cannot do a "select from" one and "insert into" another? You could probably rig it up through pipes, like a pg_dump piped to a pg_restore. -- Naomi Walker Chief Information Officer Eldorado Computing, Inc. 602-604-3100 ext 242 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Accessing different databases in a cluster
On Wed, 1 Aug 2001, Naomi Walker wrote: > At 12:22 PM 8/1/01 -0700, Stephan Szabo wrote: > >On Wed, 1 Aug 2001, Dave Blasby wrote: > > > > > Is it possible to access tables in one database from another database if > > > they're in the same cluster? I dont seem to be able to do it; is there > > > something I have to do or is it impossible? > > > >No, AFAIK, this isn't currently possible. > > Really, you cannot do a "select from" one and "insert into" another? > > You could probably rig it up through pipes, like a pg_dump piped to a > pg_restore. True, but you can't do it entirely within postgres without writing your own functions. You could do some kind of replication, but then you'd really only be accessing tables inside one database, just ones that were replicated from another. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] OID wraparound: summary and proposal
> Bruce Momjian <[EMAIL PROTECTED]> writes: > >> 6. COPY out WITH OIDS will ignore the "WITH OIDS" specification if the > >> table has no OIDs. (Alternative possibility: raise an error --- is that > >> better?) COPY in WITH OIDS will silently drop the incoming OID values. > > > Obviously, the case here is that COPY WITH OIDS alone on a non-oid table > > should throw an error, while pg_dump -o should work on a database with > > mixed oid/non-oid. I think the right thing would be to have pg_dump > > check pg_class.relhasoids and issue a proper COPY statement to match the > > existing table. > > pg_dump clearly will need to do that, so it isn't really going to be the > issue. The question is what to do when a less-clueful app issues a COPY > WITH OIDS on an OID-less table. For input, I see no downside to just > ignoring the incoming OIDs. For output, I can see three reasonable > possibilities: > > A. Pretend WITH OIDS wasn't mentioned. This might seem to be > "do the right thing", but a rather strong objection is that the > app will not get back the data it was expecting. > > B. Return NULLs or 0s for the OIDs column. > > C. Raise an error and refuse to do the copy at all. > > C is probably the most conservative answer. If we fail on load, we should fail on dump. Why not fail on COPY WITH OIDS on a non-oid table? > >> 9. To continue to support COMMENT ON COLUMN when columns have no OIDs, > >> pg_description will be modified so that its primary key is (object type, > >> object OID, column number) --- this also solves the problem that comments > >> break if there are duplicate OIDs in different system tables. The object > >> type is the OID of the system catalog in which the object OID appears. > >> The column number field will be zero for all object types except columns. > >> For a column comment, the object type and OID fields will refer to the > >> parent table, and column number will be nonzero. > > > Sounds like a hack. > > How so? pg_description is broken anyway given that we don't enforce OID > uniqueness across system catalogs. Also, in the future we could We have a script to detect them and the oid counter it unique. In what way do we not enforce it. > consider overloading the column to have meanings for > other object types. I could imagine using it to attach documentation to > each of the input arguments of a function, for example. Interesting idea. -- 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] pltcl - lastoid
> Kindly format OIDs with %u not %d ... otherwise it looks reasonable... Change made. -- 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://www.postgresql.org/search.mpl
[HACKERS] Red Hat developers
I am not sure if people noticed the signature lines, but the Toronto Red Hat developers have started submitting patches based on TODO items. Their involvement will help PostgreSQL improve even faster. Welcome aboard folks. -- 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 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] ECPG eror ...
On Wed, Aug 01, 2001 at 05:12:25PM +0700, Zudi Iswanto wrote: > I compile my c++ program to connect Postgres Sql with command line : > c++ -I /usr/local/pgsql/include -L /usr/local/pgsql/lib -lecpg -lpq -g -o capek.cgi >capek.cc > > and I ve got eror > > " Segmentation fault (core dumped) " > When do you get this? During compilation? During execution? Michael -- Michael Meskes [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Problem with FK referential actions
While looking at what needs to be done with some
of the referential actions to make them work
better under deferred constraints, I noticed something
which I think is a bug.
sszabo=> create table base (a int unique);
NOTICE: CREATE TABLE/UNIQUE will create implicit index 'base_a_key' for
table 'base'
CREATE
sszabo=> create table deriv (a int references base(a) on update cascade, b
int);
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
CREATE
sszabo=> drop index base_a_key;
DROP
/* Note: the reason I drop the unique index is because
of the brokenness of our unique constraint for the a=a+1
update below, not because I don't want the constraint. */
sszabo=> insert into base values (2);
INSERT 783232 1
sszabo=> insert into base values (3);
INSERT 783233 1
sszabo=> insert into deriv values (2,1);
INSERT 783234 1
sszabo=> insert into deriv values (3,1);
INSERT 783235 1
sszabo=> update base set a=a+1;
UPDATE 2
sszabo=> select * from deriv;
a | b
---+---
4 | 1
4 | 1
(2 rows)
The output from the select, should I believe be (3,1), (4,1)
not (4,1), (4,1). I think we're violating General Rule 4 (I think
that's it) on the referential constraint definition ("For every
row of the referenced table, its matching rows, unique matching
rows, and non-unique matching rows are determined immediately
before the execution of any SQL-statement. No new matching
rows are added during the execution of that SQL-statement.")
because when the update cascade gets done for the 2 row, we've
changed the (2,1) to (3,1) which then gets hit by the update
cascade on the 3 row.
I was wondering if you had any thoughts on an easy way around
it within what we have. :)
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
