[BUGS] missing foreign key fails silently using COPY
When COPYing data to a table which uses foreign keys, if there is a reference to a key which is not there, the copy fails (as expected) but there is no error message. Hmm. Looking at it more, seems like there is an error message when using: COPY "f" FROM '/home/lee/f.dat'; but _not_ when using: COPY "f" FROM stdin; or \copy f from f.dat This is on: PostgreSQL 7.2 on i386-unknown-freebsd4.5, compiled by GCC 2.95.3 SELECT version(); DROP TABLE p; DROP TABLE f; CREATE TABLE p ( pk int primary key ); CREATE TABLE f ( fk int references p ); COPY "p" FROM stdin; 1 2 3 4 \. COPY "f" FROM stdin; 1 2 3 4 5 \. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] missing foreign key fails silently using COPY
[EMAIL PROTECTED] (Lee Harr) writes: > When COPYing data to a table which uses foreign keys, if there > is a reference to a key which is not there, the copy fails > (as expected) but there is no error message. > Hmm. Looking at it more, seems like there is an error message > when using: > COPY "f" FROM '/home/lee/f.dat'; > but _not_ when using: > COPY "f" FROM stdin; > or > \copy f from f.dat This seems to be a libpq and/or psql bug. The error message is reported in the postmaster log and sent to the frontend --- but libpq evidently isn't catching it. I suspect that the problem is that the error is detected during transaction completion, not while the copy is in progress, and either libpq or psql is dropping the ball about reporting the error to the user. I did: regression=# create table foo(f1 int primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'foo_pkey' for table 'foo' CREATE regression=# create table bar(f2 int references foo); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) CREATE regression=# copy bar from stdin; Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. >> 1 >> \. regression=# Looking in postmaster log, I now see ERROR: referential integrity violation - key referenced from bar not found in foo but psql sure didn't tell me about it. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] missing foreign key fails silently using COPY
I wrote: > [EMAIL PROTECTED] (Lee Harr) writes: >> When COPYing data to a table which uses foreign keys, if there >> is a reference to a key which is not there, the copy fails >> (as expected) but there is no error message. >> Hmm. Looking at it more, seems like there is an error message >> when using: >> COPY "f" FROM '/home/lee/f.dat'; >> but _not_ when using: >> COPY "f" FROM stdin; >> or >> \copy f from f.dat > This seems to be a libpq and/or psql bug. After further investigation I'm still unsure where to pin the blame. What the backend is actually sending back is C COPY -- completion tag for COPY E errmsg-- error detected during xact completion Z -- backend now idle which is perfectly reasonable. What happens on the psql side is: 1. PQendcopy() eats the C COPY, stops there, and returns "success". 2. psql falls out to SendQuery, which tries to check to see if any async NOTIFY messages came in with the command. 3. PQnotifies processes the E message while looking to see if there are any N messages in the buffer. It finds none, and returns NULL. On return from PQnotifies, there is a pending asynchronous PGresult with the error message in the PGconn, and the 'Z' is still uneaten. 4. psql now goes back to sleep without any further calls to libpq. 5. On psql's next call to PQexec(), the pending error result is thrown away, as is the 'Z'; the libpq sources have the comment /* * Silently discard any prior query result that application didn't * eat. This is probably poor design, but it's here for backward * compatibility. */ However, reporting the error at this point would be far too late anyway; from the user's perspective we are now executing the next command. So I don't think PQexec is to be blamed. We could make it work without any changes in libpq by having psql do a PQgetResult after the PQendcopy, but this strikes me as an unpleasant answer; that would suggest that every application that uses PQendcopy is broken. The other line of thought is that PQendcopy should eat input until it sees the 'Z', and then return the error if there was one. This would localize the fix to PQendcopy, which would be a good thing. A drawback is that COPY TO STDIN or COPY FROM STDOUT would no longer work in the context of multiple-query strings --- though I'm doubtful that anyone uses that feature. The implications for libpq's nonblocking input mode may be bad too (though I'm unconvinced that that works at all with COPY, anyway). The existing libpq documentation says: When using PQgetResult, the application should respond to a PGRES_COPY_OUT result by executing PQgetline repeatedly, followed by PQendcopy after the terminator line is seen. It should then return to the PQgetResult loop until PQgetResult returns NULL. Similarly a PGRES_COPY_IN result is processed by a series of PQputline calls followed by PQendcopy, then return to the PQgetResult loop. This arrangement will ensure that a copy in or copy out command embedded in a series of SQL commands will be executed correctly. Older applications are likely to submit a copy in or copy out via PQexec and assume that the transaction is done after PQendcopy. This will work correctly only if the copy in/out is the only SQL command in the command string. This seems to lean more in the direction of thinking that psql should do a PQgetResult after the PQendcopy. Perhaps we should do that, and add a warning to the docs that PQendcopy alone is insufficient to detect end-of-transaction errors. In any case it's a bit of a mess :-( Comments anyone? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] Possible bug concerning LASTOID in 7.2
Coax <[EMAIL PROTECTED]> writes:
> [ insert with an ON INSERT DO UPDATE ... rule ]
> Here's the output of PG7.1.2:
> test=# INSERT INTO forum
>(group_forum_id,posted_by,subject,body,date,is_followup_to,thread_id,most_recent_date)
> VALUES ('88760', '192470', 'asdfasdfsqdf', 'blah',
>'1014405669','0','625864','1014405669');
> INSERT 80942390 1
> test=# \echo :LASTOID
> 80942390
> However, here's the output from PG7.2:
> test=# INSERT INTO forum
>(group_forum_id,posted_by,subject,body,date,is_followup_to,thread_id,most_recent_date)
> VALUES ('88760', '192470', 'asdfasdfsqdf', 'blah',
>'1014405669','0','625864','1014405669')
> test-# ;
> UPDATE 0
> test=# \echo :LASTOID
> 0
I suspect the proximate cause of this is that we changed 7.2 so that
rule actions for an INSERT are done after the INSERT, not beforehand.
This is a good idea from most points of view, but it's annoying that
it's affecting the returned command tag. I tend to agree that that's
a bug, and that the tag ought to reflect the given command not any
queries generated by rules. However, it might be difficult to fix.
Fernando, does your command-tag patch help with this at all?
Another thing to think about: if the given query is suppressed entirely
by an ON INSERT DO INSTEAD rule, what tag should be returned?
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [BUGS] Full bug list
Hi Andy, I agree with you totally. Don't yet know how to implement it, and don't have much spare time to do so yet either. I'm working on some stuff which will mean there are more people to help with the techdocs site and get things done (if it works), therefore improving the present techdocs system of showing known bugs and fixes, etc. But it's going to take a few months of effort. :-) Regards and best wishes, Justin Clift On Friday 22 February 2002 23:17, Andy Marden wrote: > It would be useful to be able to search a list of known bugs for > the version of postgresql that you're running. Upgrading to the > latest version to see if it sorts out a problem isn't always a > sensible option.. > Andy > - Original Message - > From: "Justin" <[EMAIL PROTECTED]> > To: "Andy Marden" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > Sent: Friday, February 22, 2002 3:24 AM > Subject: Re: Full bug list > > > Hi Andy, > > > > Thats the reason which techdocs.postgresql.org started for, > > but since it's > > > grown to encompass so many things, that section hasn't been > > updated in ages. > > > :-( > > > > Perhaps its time to look at a better way of keeping it > > updated? > > > :-) > > > > Regards and best wishes, > > > > Justin Clift > > > > On Thursday 21 February 2002 20:32, Andy Marden wrote: > > > Is there a repository on the web somewhere of all the bugs > > found, versions > > > > they are found against and what theire status is? > > > > > > > > > > > > ---(end of > > broadcast)--- > > > > TIP 2: you can get off all lists at once with the unregister > > command > > > > (send "unregister YourEmailAddressHere" to > > [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [BUGS] Trying Cygwin version of PostgreSQL again
Hi Peter, Haven't yet had the time to properly try this out. Did a quick initial installation of PostgreSQL 7.2 with cygwin 1.3.9, and got the same error as yourself. I've had reports of PostgreSQL 7.1.3 working on cygwin however, but haven't gotten around to trying that myself yet. There is a possibility that the 7.2 version of PostgreSQL that's made for cygwin might not yet be at a point where it should be, due to PostgreSQL 7.2 only very recently being released. There is a PostgreSQL "cygwin" mailing list which you can subscribe to and ask the guys there about, and this sounds like it might be the best avenue for you : http://www.ca.postgresql.org/users-lounge/index.html Hoping that helps. :-) Regards and best wishes, Justin Clift On Friday 22 February 2002 16:44, Peter wrote: > Justin Clift suggested: > http://www.ejip.net/faq/postgresql_win_setup_faq.jsp > I started from scratch following the page and adapting it where it is > out of date. I reached the stage of starting postmaster and receiving > the message: > DEBUG: pq_recvbuf: unexpected EOF on client connection > DEBUG: incomplete startup packet > > Peter > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[BUGS] COPY FROM is not 8bit clean
When useing COPY FROM 'file' ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[BUGS] COPY FROM is not 8bit clean
ACK! must rember which MTA I'm useing... When useing COPY FROM 'file' DELIMITER '\254' copyfrom reads past the delimiter and ends up with parse errors when trying to do the insert ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[BUGS] COPY FROM is not 8bit clean
ACK! must rember which MTA I'm useing... When useing COPY FROM 'file' DELIMITER '\254' copyfrom reads past the delimiter and ends up with parse errors when trying to do the insert What the ?? why dind' tthat go through with the body of the text.. *sigh* I'll resend in the AM.. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] COPY FROM is not 8bit clean
Darcy Buskermolen <[EMAIL PROTECTED]> writes: > When useing COPY FROM 'file' DELIMITER '\254' copyfrom reads past the > delimiter and ends up with parse errors when trying to do the insert Are you perhaps operating in a multibyte encoding in which \254 is just the first byte of a multibyte character? I'm not sure what we do in such a case, and even less sure what we should do ... but I am entirely prepared to believe that we don't do the Right Thing ... 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
