Обсуждение: insert ... delete ... returning ... ?
Hi all: I'm at PostgreSQL 8.3 for my production database and everything is working great. I had no problems converting free text search from 8.2 to 8.3, and I really like the improvements. I tried using insert ... delete ... return ... and get a syntax error: > pccyber=# insert into product_manufacturer_archived (itemno, > manufacturer_id) > pccyber-# delete from product_manufacturer > pccyber-# where not exists (select * from icitem > pccyber(# where icitem.itemno = > product_manufacturer.itemno and > pccyber(# not inactive) > pccyber-# returning itemno, manufacturer_id; > ERROR: syntax error at or near "delete" > LINE 2: delete from product_manufacturer > ^ The goal here is to move inactive records to an archived table. This is to be performed as part of a daily batch job instead of as a trigger. Assume my model is correct - my question isn't how can I do this. I would like to know if insert .. delete .. returning is intended to work or not. In the past I've executed insert ... select and then the delete. However, I believe there is race condition here as the delete may see more or less rows than the insert ... select. I thought the above would be a clever PostgreSQL-8.3 alternative, but I'm either stupid or it doesn't work... :-) Any ideas? Thanks, mark -- Mark Mielke <mark@mielke.cc>
Hi Mark, On Sun, Feb 24, 2008 at 10:21 PM, Mark Mielke <mark@mark.mielke.cc> wrote: > I'm at PostgreSQL 8.3 for my production database and everything is > working great. I had no problems converting free text search from 8.2 to > 8.3, and I really like the improvements. > > I tried using insert ... delete ... return ... and get a syntax error: > > > pccyber=# insert into product_manufacturer_archived (itemno, > > manufacturer_id) I would expect a semicolon here. > > pccyber-# delete from product_manufacturer > > pccyber-# where not exists (select * from icitem > > pccyber(# where icitem.itemno = > > product_manufacturer.itemno and > > pccyber(# not inactive) > > pccyber-# returning itemno, manufacturer_id; > > ERROR: syntax error at or near "delete" > > LINE 2: delete from product_manufacturer > > ^ -- Guillaume
On Sun, Feb 24, 2008 at 10:39 PM, Guillaume Smet <guillaume.smet@gmail.com> wrote: > On Sun, Feb 24, 2008 at 10:21 PM, Mark Mielke <mark@mark.mielke.cc> wrote: > > I'm at PostgreSQL 8.3 for my production database and everything is > > working great. I had no problems converting free text search from 8.2 to > > 8.3, and I really like the improvements. > > > > I tried using insert ... delete ... return ... and get a syntax error: > > > > > pccyber=# insert into product_manufacturer_archived (itemno, > > > manufacturer_id) > > I would expect a semicolon here. Mmmmh, my bad, I missed your point. Sorry for the noise :). -- Guillaume
On Sun, Feb 24, 2008 at 4:21 PM, Mark Mielke <mark@mark.mielke.cc> wrote: > I tried using insert ... delete ... return ... and get a syntax error: Yeah... > In the past I've executed insert ... select and then the delete. > However, I believe there is race condition here as the delete may see > more or less rows than the insert ... select. I thought the above would > be a clever PostgreSQL-8.3 alternative, but I'm either stupid or it > doesn't work... :-) Not stupid, it doesn't work :) This was a limitation of the original design based on (IIRC) executor-related issues. I've seen about 6 or so posts now about using DELETE returning in the same manner as you're discussing, and I agree it would be quite useful. Unfortunately, with the amount of changes required to make it work properly, no one has wanted to pick that up and add it yet :( Depending on what else I'm working on, I'd like to get this fixed for 8.4. Though, I'll probably be working on other, more important projects. -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation | fax: 732.331.1301 499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com Edison, NJ 08837 | http://www.enterprisedb.com/
"Jonah H. Harris" <jonah.harris@gmail.com> writes:
> Not stupid, it doesn't work :) This was a limitation of the original
> design based on (IIRC) executor-related issues.
There are definitional issues not only implementation ones; in
particular, in subquery-like cases it's entirely unclear how many times
the DML operation will or should get evaluated.
regards, tom lane
On Sun, Feb 24, 2008 at 5:44 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Jonah H. Harris" <jonah.harris@gmail.com> writes: > > Not stupid, it doesn't work :) This was a limitation of the original > > design based on (IIRC) executor-related issues. > > There are definitional issues not only implementation ones; in > particular, in subquery-like cases it's entirely unclear how many times > the DML operation will or should get evaluated. Yup,that's what it was. I think I remember the trigger-level and top-level executor-related stuff. If I'm in that area of the code soon, I'll see how much would be involved and if I think I have enough time, submit a proposal for it. -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation | fax: 732.331.1301 499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com Edison, NJ 08837 | http://www.enterprisedb.com/
Tom Lane wrote: <blockquote cite="mid:8368.1203893092@sss.pgh.pa.us" type="cite"><pre wrap="">"Jonah H. Harris" <a class="moz-txt-link-rfc2396E"href="mailto:jonah.harris@gmail.com"><jonah.harris@gmail.com></a> writes: </pre><blockquotetype="cite"><pre wrap="">Not stupid, it doesn't work :) This was a limitation of the original design based on (IIRC) executor-related issues. </pre></blockquote><pre wrap=""> There are definitional issues not only implementation ones; in particular, in subquery-like cases it's entirely unclear how many times the DML operation will or should get evaluated. </pre></blockquote><br /> Interesting. Would it be cheating to only allowit in cases where the evaluation should definately be only once? For example, insert ... delete, create table ... delete,or part of a join expression?<br /><br /> In any case - I don't have the know how to fix it, and it's certainly moreof a "would be cute" than "I must have it." I'll settle with my table locks for now. It's no big deal for my application.<br/><br /> I'm noticing a massive reduction in on disk storage required for my database that I believe is primarilyattributable due to Tom's reduced overhead for short strings. Some of the tables I am importing have a 10 - 20 shortstring fields (many 0 length strings!). Unfortunately - I wasn't looking for this specifically, so I didn't keep myold database instance around. But I'm thinking by memory that the biggest table is now 1/3 the number of relpages in 8.3as it was in 8.2. Good job all around hackers. Again - *NO* problems. It just works.<br /><br /> Cheers,<br /> mark<br/><br /><pre class="moz-signature" cols="72">-- Mark Mielke <a class="moz-txt-link-rfc2396E" href="mailto:mark@mielke.cc"><mark@mielke.cc></a> </pre>
Mark Mielke <mark@mark.mielke.cc> writes:
> I'm noticing a massive reduction in on disk storage required for my
> database that I believe is primarily attributable due to Tom's reduced
> overhead for short strings.
Twasn't my work; Greg Stark gets most of the credit for that one, and
you might be seeing some benefit from Heikki's work to cut the tuple
header size too.
regards, tom lane
Tom Lane wrote: <blockquote cite="mid:12952.1203920527@sss.pgh.pa.us" type="cite"><pre wrap="">Mark Mielke <a class="moz-txt-link-rfc2396E"href="mailto:mark@mark.mielke.cc"><mark@mark.mielke.cc></a> writes: </pre><blockquotetype="cite"><pre wrap="">I'm noticing a massive reduction in on disk storage required for my database that I believe is primarily attributable due to Tom's reduced overhead for short strings. </pre></blockquote><pre wrap=""> Twasn't my work; Greg Stark gets most of the credit for that one, and you might be seeing some benefit from Heikki's work to cut the tuple header size too. </pre></blockquote><br /> Oops. You are right. Thanks Greg and Heikki! Whatever you did works great! :-)<br/><br /> Cheers,<br /> mark<br /><br /><pre class="moz-signature" cols="72">-- Mark Mielke <a class="moz-txt-link-rfc2396E" href="mailto:mark@mielke.cc"><mark@mielke.cc></a> </pre>
Added to TODO: * Allow INSERT ... DELETE ... RETURNING, namely allow the DELETE ... RETURNING to supply values to the INSERT http://archives.postgresql.org/pgsql-hackers/2008-02/thrd2.php#00979 --------------------------------------------------------------------------- Jonah H. Harris wrote: > On Sun, Feb 24, 2008 at 5:44 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > "Jonah H. Harris" <jonah.harris@gmail.com> writes: > > > Not stupid, it doesn't work :) This was a limitation of the original > > > design based on (IIRC) executor-related issues. > > > > There are definitional issues not only implementation ones; in > > particular, in subquery-like cases it's entirely unclear how many times > > the DML operation will or should get evaluated. > > Yup,that's what it was. I think I remember the trigger-level and > top-level executor-related stuff. If I'm in that area of the code > soon, I'll see how much would be involved and if I think I have enough > time, submit a proposal for it. > > -- > Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 > EnterpriseDB Corporation | fax: 732.331.1301 > 499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com > Edison, NJ 08837 | http://www.enterprisedb.com/ > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +