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(at)mielke(dot)cc>

Hi Mark,

On Sun, Feb 24, 2008 at 10:21 PM, Mark Mielke <mark(at)mark(dot)mielke(dot)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(dot)smet(at)gmail(dot)com> wrote:
> On Sun, Feb 24, 2008 at 10:21 PM, Mark Mielke <mark(at)mark(dot)mielke(dot)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(at)mark(dot)mielke(dot)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(dot)harris(at)enterprisedb(dot)com
Edison, NJ 08837 | http://www.enterprisedb.com/

"Jonah H. Harris" <jonah(dot)harris(at)gmail(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Jonah H. Harris" <jonah(dot)harris(at)gmail(dot)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(dot)harris(at)enterprisedb(dot)com
Edison, NJ 08837 | http://www.enterprisedb.com/

Tom Lane wrote:
> "Jonah H. Harris" <jonah(dot)harris(at)gmail(dot)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.
>

Interesting. Would it be cheating to only allow it in cases where the
evaluation should definately be only once? For example, insert ...
delete, create table ... delete, or part of a join expression?

In any case - I don't have the know how to fix it, and it's certainly
more of 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.

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. Some of the tables I am importing have a 10
- 20 short string fields (many 0 length strings!). Unfortunately - I
wasn't looking for this specifically, so I didn't keep my old database
instance around. But I'm thinking by memory that the biggest table is
now 1/3 the number of relpages in 8.3 as it was in 8.2. Good job all
around hackers. Again - *NO* problems. It just works.

Cheers,
mark

--
Mark Mielke <mark(at)mielke(dot)cc>

Mark Mielke <mark(at)mark(dot)mielke(dot)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:
> Mark Mielke <mark(at)mark(dot)mielke(dot)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.
>

Oops. You are right. Thanks Greg and Heikki! Whatever you did works
great! :-)

Cheers,
mark

--
Mark Mielke <mark(at)mielke(dot)cc>

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > "Jonah H. Harris" <jonah(dot)harris(at)gmail(dot)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(dot)harris(at)enterprisedb(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +