pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
bug or feature?
7+ messages / 7 participants
[nested] [flat]

* bug or feature?
@ 1999-11-25 16:54 Reto H. <no_spam_4me_cut_here_retoh@dplanet.ch>
  0 siblings, 0 replies; 7+ messages in thread

From: Reto H. @ 1999-11-25 16:54 UTC (permalink / raw)
  To: pgsql-general@postgresql.org.pgsql-patches

Data type 'ABSTIME' seems to have a kind of Y2K bug

Highest date you can store is Dec 31, 2011

Is there a patch out there?

Thanks for any suggestions.

<Reto> retoh@dplanet.ch





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Bug or feature?
@ 2002-05-20 11:15 Kostyuk Oleg <cub@digma.kharkov.ua>
  2002-05-21 03:13 ` Re: Bug or feature? Stephan Szabo <sszabo@megazone23.bigpanda.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Kostyuk Oleg @ 2002-05-20 11:15 UTC (permalink / raw)
  To: pgsql-general

pgSQL 7.2.1, table with ~590000 rows with next stuct:

user_id       integer
url           text
.................... -- other fields

i do VACUUM ANALYZE, and then have this results:


1) ----------
proxy=> EXPLAIN SELECT count(user_id) AS cnt, user_id FROM stats GROUP 
BY user_id ORDER BY cnt DESC;
NOTICE:  QUERY PLAN:

Sort  (cost=94399.52..94399.52 rows=58842 width=4)
   ->  Aggregate  (cost=86383.49..89325.61 rows=58842 width=4)
         ->  Group  (cost=86383.49..87854.55 rows=588425 width=4)
               ->  Sort  (cost=86383.49..86383.49 rows=588425 width=4)
                     ->  Seq Scan on stats  (cost=0.00..13873.25 
rows=588425 width=4)


2) ----------
proxy=> EXPLAIN SELECT count(url) AS cnt, user_id FROM stats GROUP BY 
user_id ORDER BY cnt DESC;
NOTICE:  QUERY PLAN:

Sort  (cost=155022.05..155022.05 rows=58842 width=51)
   ->  Aggregate  (cost=146163.49..149105.61 rows=58842 width=51)
         ->  Group  (cost=146163.49..147634.55 rows=588425 width=51)
               ->  Sort  (cost=146163.49..146163.49 rows=588425 width=51)
                     ->  Seq Scan on stats  (cost=0.00..13873.25 
rows=588425 width=51)

-----------end-----------

cost grow by 1.5 times!
yes, i understand - this because url column have big width...
but in count(url) width of column not important! - but used?...
or it's bug of optimizer? or it's not implemented in optimizer?

Thx.

ps : sorry for my bad english :)

-- 
With best wishes, DIGMA sysadmin
Oleg Kostyuk aka Cub (CUB7-RIPE)
[BSD registered user #BSD050664]




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Bug or feature?
  2002-05-20 11:15 Bug or feature? Kostyuk Oleg <cub@digma.kharkov.ua>
@ 2002-05-21 03:13 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Stephan Szabo @ 2002-05-21 03:13 UTC (permalink / raw)
  To: Kostyuk Oleg <cub@digma.kharkov.ua>; +Cc: pgsql-general

On Mon, 20 May 2002, Kostyuk Oleg wrote:

> 1) ----------
> proxy=> EXPLAIN SELECT count(user_id) AS cnt, user_id FROM stats GROUP
> BY user_id ORDER BY cnt DESC;
> NOTICE:  QUERY PLAN:
>
> Sort  (cost=94399.52..94399.52 rows=58842 width=4)
>    ->  Aggregate  (cost=86383.49..89325.61 rows=58842 width=4)
>          ->  Group  (cost=86383.49..87854.55 rows=588425 width=4)
>                ->  Sort  (cost=86383.49..86383.49 rows=588425 width=4)
>                      ->  Seq Scan on stats  (cost=0.00..13873.25
> rows=588425 width=4)
>
>
> 2) ----------
> proxy=> EXPLAIN SELECT count(url) AS cnt, user_id FROM stats GROUP BY
> user_id ORDER BY cnt DESC;
> NOTICE:  QUERY PLAN:
>
> Sort  (cost=155022.05..155022.05 rows=58842 width=51)
>    ->  Aggregate  (cost=146163.49..149105.61 rows=58842 width=51)
>          ->  Group  (cost=146163.49..147634.55 rows=588425 width=51)
>                ->  Sort  (cost=146163.49..146163.49 rows=588425 width=51)
>                      ->  Seq Scan on stats  (cost=0.00..13873.25
> rows=588425 width=51)
>
> -----------end-----------
>
> cost grow by 1.5 times!

In general, I'm not sure how meaningful a comparison between explain
outputs from different queries are.

> yes, i understand - this because url column have big width...
> but in count(url) width of column not important! - but used?...

Except that it's probably brought along for the sort step.  You
do at least need to know if it's NULL or not, and there's nothing
I can't think of something that would necessarily stop you from redefining
a count aggregate that did something with that value (apart from the
fact it's probably silly).  There are optimizations with certain
aggregates that aren't taken because the aggregate system is pretty
general.




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* bug or feature
@ 2026-10-05 08:26 Carl.Weierstrass <carl.weierstrass@googlemail.com>
  2026-10-05 14:12 ` Re: bug or feature Laurenz Albe <laurenz.albe@cybertec.at>
  2026-10-05 14:32 ` Re: bug or feature David G. Johnston <david.g.johnston@gmail.com>
  0 siblings, 2 replies; 7+ messages in thread

From: Carl.Weierstrass @ 2026-10-05 08:26 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

Hi,

I want to delete some data from table test1 und protocol the deleted 
data in table test2:

The solution with two statements

begin;
insert into test2
select * from test1
where 1 = 1;

delete from test1
where 1 = 1
returning *;   -- show deleted lines
commit;

works fine, but the more elegant version in one single statement results 
in a syntax error

begin;
insert into test2
delete from test1
where 1 = 1
returning *;  --return deleted lines
commit;

psql:test.sql:32: FEHLER:  Syntaxfehler bei »delete«
ZEILE 2: delete from test1

Is this a bug or a feature?


Charly









^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: bug or feature
  2026-10-05 08:26 bug or feature Carl.Weierstrass <carl.weierstrass@googlemail.com>
@ 2026-10-05 14:12 ` Laurenz Albe <laurenz.albe@cybertec.at>
  1 sibling, 0 replies; 7+ messages in thread

From: Laurenz Albe @ 2026-10-05 14:12 UTC (permalink / raw)
  To: Carl.Weierstrass <carl.weierstrass@googlemail.com>; pgsql-general@lists.postgresql.org

On Mon, 2026-10-05 at 10:26 +0200, Carl.Weierstrass wrote:
> I want to delete some data from table test1 und protocol the deleted 
> data in table test2:
> 
> The solution with two statements
> 
> begin;
> insert into test2
> select * from test1
> where 1 = 1;
> 
> delete from test1
> where 1 = 1
> returning *;   -- show deleted lines
> commit;
> 
> works fine, but the more elegant version in one single statement results 
> in a syntax error
> 
> begin;
> insert into test2
> delete from test1
> where 1 = 1
> returning *;  --return deleted lines
> commit;
> 
> psql:test.sql:32: FEHLER:  Syntaxfehler bei »delete«
> ZEILE 2: delete from test1
> 
> Is this a bug or a feature?

That's incorrect syntax.

Try

  WITH cte AS (
     DELETE FROM test1
     RETURNING *
  )
  INSERT INTO test2
  SELECT * FROM cte;

Yours,
Laurenz Albe






^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: bug or feature
  2026-10-05 08:26 bug or feature Carl.Weierstrass <carl.weierstrass@googlemail.com>
@ 2026-10-05 14:32 ` David G. Johnston <david.g.johnston@gmail.com>
  2026-10-05 14:53   ` Re: bug or feature Adrian Klaver <adrian.klaver@aklaver.com>
  1 sibling, 1 reply; 7+ messages in thread

From: David G. Johnston @ 2026-10-05 14:32 UTC (permalink / raw)
  To: Carl.Weierstrass <carl.weierstrass@googlemail.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

On Monday, October 5, 2026, Carl.Weierstrass <
carl.weierstrass@googlemail.com> wrote:

> but the more elegant version in one single statement results in a syntax
> error
>
> begin;
> insert into test2
> delete from test1
> where 1 = 1
> returning *;  --return deleted lines
> commit;
>
> psql:test.sql:32: FEHLER:  Syntaxfehler bei »delete«
> ZEILE 2: delete from test1
>
> Is this a bug or a feature?


If you make something up that isn’t documented it shouldn’t work.  You can
call the inability to interpret random stuff you send to the server a
feature if you need an apply a label.

You might want to look at the WITH clause of those commands to see how to
go about combining multiple distinct commands into one execution correctly.

David J.

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: bug or feature
  2026-10-05 08:26 bug or feature Carl.Weierstrass <carl.weierstrass@googlemail.com>
  2026-10-05 14:32 ` Re: bug or feature David G. Johnston <david.g.johnston@gmail.com>
@ 2026-10-05 14:53   ` Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Adrian Klaver @ 2026-10-05 14:53 UTC (permalink / raw)
  To: David G. Johnston <david.g.johnston@gmail.com>; Carl.Weierstrass <carl.weierstrass@googlemail.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

On 10/5/26 7:32 AM, David G. Johnston wrote:
> On Monday, October 5, 2026, Carl.Weierstrass 
> <carl.weierstrass@googlemail.com 
> <mailto:carl.weierstrass@googlemail.com>> wrote:
> 
>     but the more elegant version in one single statement results in a
>     syntax error
> 
>     begin;
>     insert into test2
>     delete from test1
>     where 1 = 1
>     returning *;  --return deleted lines
>     commit;
> 
>     psql:test.sql:32: FEHLER:  Syntaxfehler bei »delete«
>     ZEILE 2: delete from test1
> 
>     Is this a bug or a feature?
> 
> 
> If you make something up that isn’t documented it shouldn’t work.  You 
> can call the inability to interpret random stuff you send to the server 
> a feature if you need an apply a label.
> 
> You might want to look at the WITH clause of those commands to see how 
> to go about combining multiple distinct commands into one execution 
> correctly.

In particular:

https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-MODIFYING

> 
> David J.
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com






^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2026-10-05 14:53 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-11-25 16:54 bug or feature? Reto H. <no_spam_4me_cut_here_retoh@dplanet.ch>
2002-05-20 11:15 Bug or feature? Kostyuk Oleg <cub@digma.kharkov.ua>
2002-05-21 03:13 ` Re: Bug or feature? Stephan Szabo <sszabo@megazone23.bigpanda.com>
2026-10-05 08:26 bug or feature Carl.Weierstrass <carl.weierstrass@googlemail.com>
2026-10-05 14:12 ` Re: bug or feature Laurenz Albe <laurenz.albe@cybertec.at>
2026-10-05 14:32 ` Re: bug or feature David G. Johnston <david.g.johnston@gmail.com>
2026-10-05 14:53   ` Re: bug or feature Adrian Klaver <adrian.klaver@aklaver.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox