feature request
hello to everyone,
is a bit late for an italian, but after an long day debugging I had an idea.
Why not introduce a special SQL command like
STORE WHERE [condition] FROM [table]
removing all data that meet the condition and storing them into another database?
Then, if a query that needs the stored data is executed after such command the database joins the stored data into the result query.
This can keep the production database lightweight and fast.
Regards
Omar Bettin
On Thu, Feb 25, 2010 at 4:35 PM, Omar Bettin
<o.bettin@informaticaindustriale.it> wrote:
hello to everyone,
is a bit late for an italian, but after an long day debugging I had an idea.
Why not introduce a special SQL command like
STORE WHERE [condition] FROM [table]
removing all data that meet the condition and storing them into another
database?
Then, if a query that needs the stored data is executed after such command
the database joins the stored data into the result query.
This can keep the production database lightweight and fast.
Regards
DELETE ... RETURNING is useful for this kind of thing, sometimes. And
you could use it inside a function to go and do something with each
row returned, though that might not be very graceful for large numbers
of rows. The proposed syntax wouldn't actually work because it
doesn't specify where to put the data.
...Robert
...could be
STORE WHERE [condition] FROM [table] INTO [database]
regards
Omar Bettin
----- Original Message -----
From: "Robert Haas" <robertmhaas@gmail.com>
To: "Omar Bettin" <o.bettin@informaticaindustriale.it>
Cc: <pgsql-hackers@postgresql.org>
Sent: Thursday, February 25, 2010 11:11 PM
Subject: Re: [HACKERS] feature request
Show quoted text
On Thu, Feb 25, 2010 at 4:35 PM, Omar Bettin
<o.bettin@informaticaindustriale.it> wrote:hello to everyone,
is a bit late for an italian, but after an long day debugging I had an
idea.
Why not introduce a special SQL command like
STORE WHERE [condition] FROM [table]
removing all data that meet the condition and storing them into another
database?
Then, if a query that needs the stored data is executed after such
command
the database joins the stored data into the result query.
This can keep the production database lightweight and fast.
RegardsDELETE ... RETURNING is useful for this kind of thing, sometimes. And
you could use it inside a function to go and do something with each
row returned, though that might not be very graceful for large numbers
of rows. The proposed syntax wouldn't actually work because it
doesn't specify where to put the data....Robert
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Thu, Feb 25, 2010 at 5:17 PM, Omar Bettin
<o.bettin@informaticaindustriale.it> wrote:
...could be
STORE WHERE [condition] FROM [table] INTO [database]
That still doesn't work, because a PostgreSQL backend doesn't have any
obvious way to access another database. You'd need to use dblink or
something. Eventually (but not any time soon) it will probably be
possible to do things like this, which would work for moving data
between tables in the same database:
WITH x AS (DELETE FROM ... RETURNING ...) INSERT INTO ... (...) SELECT
... FROM x
Doing anything with some OTHER database is going to require a lot more
infrastructure.
...Robert
I have read that 8.5 will have replication, so is just a feature request.
regards
Omar Bettin
----- Original Message -----
From: "Robert Haas" <robertmhaas@gmail.com>
To: "Omar Bettin" <o.bettin@informaticaindustriale.it>
Cc: <pgsql-hackers@postgresql.org>
Sent: Thursday, February 25, 2010 11:22 PM
Subject: Re: [HACKERS] feature request
Show quoted text
On Thu, Feb 25, 2010 at 5:17 PM, Omar Bettin
<o.bettin@informaticaindustriale.it> wrote:...could be
STORE WHERE [condition] FROM [table] INTO [database]That still doesn't work, because a PostgreSQL backend doesn't have any
obvious way to access another database. You'd need to use dblink or
something. Eventually (but not any time soon) it will probably be
possible to do things like this, which would work for moving data
between tables in the same database:WITH x AS (DELETE FROM ... RETURNING ...) INSERT INTO ... (...) SELECT
... FROM xDoing anything with some OTHER database is going to require a lot more
infrastructure....Robert