Thread: create view
Hello,
I've found a way to crash PostgreSQL, using a self-refering view:
CREATE TABLE phy_tree_node (
node_id int primary key,
parent_id int references phy_tree_node(node_id),
node_name varchar(64)
);
CREATE VIEW phy_node_name AS
SELECT node_id, parent_id, CAST(node_name AS text) FROM phy_tree_node;
CREATE OR REPLACE VIEW phy_node_name AS
SELECT node_id, parent_id,
CAST(
CASE WHEN c.parent_id IS NOT NULL THEN
(SELECT node_name FROM phy_node_name AS p
WHERE p.node_id = c.parent_id)
ELSE ''
END
|| '/' || c.node_name AS text) AS node_name
FROM phy_tree_node AS c;
When I try to do any select from it, I get the following:
psql=> SELECT * FROM phy_tree_node WHERE node_id = 6;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
In my serverlog, I have the following:
LOG: server process (pid 23461) was terminated by signal 11
LOG: terminating any other active server processes
LOG: all server processes terminated; reinitializing shared memory and semaphores
LOG: database system was interrupted at 2003-02-25 12:15:30 EST
LOG: checkpoint record is at 0/2C1ECAEC
LOG: redo record is at 0/2C1ECAEC; undo record is at 0/0; shutdown TRUE
LOG: next transaction id: 941978; next oid: 320082
LOG: database system was not properly shut down; automatic recovery in progress
LOG: redo starts at 0/2C1ECB2C
LOG: ReadRecord: record with zero length at 0/2C215334
LOG: redo done at 0/2C215310
FATAL: The database system is starting up
LOG: database system is ready
(Note: I like the "FATAL" error: 'The database system is starting up').
And, no, i'm not suprised that what I was trying to do caused an error.
If anyone cares, my phy_tree_node is a tree structure, and I'm trying to
figure a way that I can have make a report in crystal reports that displays
the full path of the document.
Thanks!
Ryan
--
Ryan VanderBijl rvbijl@vanderbijlfamily.com
Ryan VanderBijl <rvbijl-pgsql@vanderbijlfamily.com> writes:
> I've found a way to crash PostgreSQL, using a self-refering view:
This is what comes of saying "oh, adding a REPLACE option to CREATE
is trivial and requires no thought" :-(
While we could check for and disallow direct self-references pretty
easily, I don't think that solves the problem --- one could
still construct infinite recursions involving multiple views.
AFAICS the only real defense is to add a run-time check for recursion
in ApplyRetrieveRule. Annoying; anyone see a better answer?
regards, tom lane
On Tue, 2003-02-25 at 11:27, Ryan VanderBijl wrote: > (Note: I like the "FATAL" error: 'The database system is starting up'). That's a valid error: psql tries to reconnect to the server when the original connection to the backend is terminated, but the reconnect attempt is received while the postmaster is still restarting. So the reconnect attempt is refused, and the error is "fatal" to the reconnect attempt. Cheers, Neil -- Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
I wrote:
> Ryan VanderBijl <rvbijl-pgsql@vanderbijlfamily.com> writes:
>> I've found a way to crash PostgreSQL, using a self-refering view:
> AFAICS the only real defense is to add a run-time check for recursion
> in ApplyRetrieveRule. Annoying; anyone see a better answer?
This turned out to be less painful/expensive than I thought, so there's
now a runtime recursion check in CVS tip:
regression=# SELECT * FROM phy_node_name WHERE node_id = 6;
ERROR: Infinite recursion detected in rules for relation phy_node_name
I don't feel a need to back-patch this into 7.3.*, though; the problem
seems to me to not be worth taking a risk of breaking things.
regards, tom lane
Hi all, We have a function that runs under cron every 5 minutes. It creates a lot of temp tables. The Function slows down during the day considerably. I suspect it is due the constant creation and dropping of temp tables. If I vacuum full pg_attribute and pg_class it fixes the problem. But unfortunately vacuuming these table every run is not practical as it takes so much time. If I were to create the temp tables without oids, does this mean both pg_attribute and pg_class will not be as affected. If so, can I specify without oids when creating a temp table as follows? create temp table test as select * from test2 Thanks Andrew Bartley
On Tue, 2003-02-25 at 19:59, Andrew Bartley wrote: > The Function slows down during the day considerably. I suspect it is due > the constant creation and dropping of temp tables. If I vacuum full > pg_attribute and pg_class it fixes the problem. Does a regular VACUUM accomplish the same result? > But unfortunately vacuuming these table every run is not practical as > it takes so much time. Can you elaborate? VACUUMing *just* the pg_attribute and pg_class tables takes an excessive amount of time? (You're aware you can VACUUM specific tables, as well as entire databases, right?) > If I were to create the temp tables without oids, does this mean both > pg_attribute and pg_class will not be as affected. AFAIK, it wouldn't affect either significantly. Cheers, Neil -- Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
On Tuesday 25 February 2003 4:59 pm, Andrew Bartley wrote: > The Function slows down during the day considerably. I suspect it is due > the constant creation and dropping of temp tables. If I vacuum full > pg_attribute and pg_class it fixes the problem. But unfortunately > vacuuming these table every run is not practical as it takes so much time. Try regular vacuum - that should mark unused space in those tables as reusable so the table size should stabilize instead of growing. > create temp table test as select * from test2 If you are always creating the same tables then could you might (if there aren't locking issues) be able to set up empty copies of the tables and do something like: truncate table scratch_test; insert into scratch_test select * from test2; But the bigger question is what is it that you do that is creating all these temp tables so often? Perhaps there is a completely different better alternative. Cheers, Steve
Thanks Steve, A regular vacuum of the pg_attribute and pg_class tables still takes too long. Regarding the Idea of using scratch tables, I have also tried. It seems that if I use temp tables in the process it runs a lot faster... why, I'm not sure. I have also found that using truncate slows down during the day and It also presents it's own problems with rolling back changes. Do you know what the down side is of using WITHOUT OIDS? And will it help the problem of growing pg tables? ----- Original Message ----- From: "Steve Crawford" <scrawford@pinpointresearch.com> To: "Andrew Bartley" <abartley@evolvosystems.com>; <pgsql-general@postgresql.org> Sent: Thursday, February 27, 2003 7:01 AM Subject: Re: [GENERAL] WITHOUT OIDS > On Tuesday 25 February 2003 4:59 pm, Andrew Bartley wrote: > > > The Function slows down during the day considerably. I suspect it is due > > the constant creation and dropping of temp tables. If I vacuum full > > pg_attribute and pg_class it fixes the problem. But unfortunately > > vacuuming these table every run is not practical as it takes so much time. > > Try regular vacuum - that should mark unused space in those tables as > reusable so the table size should stabilize instead of growing. > > > create temp table test as select * from test2 > > If you are always creating the same tables then could you might (if there > aren't locking issues) be able to set up empty copies of the tables and do > something like: > > truncate table scratch_test; > insert into scratch_test select * from test2; > > But the bigger question is what is it that you do that is creating all these > temp tables so often? Perhaps there is a completely different better > alternative. > > Cheers, > Steve > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > >
----- Original Message ----- From: "Neil Conway" <neilc@samurai.com> To: "Andrew Bartley" <abartley@evolvosystems.com> Cc: "PostgreSQL General" <pgsql-general@postgresql.org> Sent: Wednesday, February 26, 2003 5:40 PM Subject: Re: [GENERAL] WITHOUT OIDS > On Tue, 2003-02-25 at 19:59, Andrew Bartley wrote: > > The Function slows down during the day considerably. I suspect it is due > > the constant creation and dropping of temp tables. If I vacuum full > > pg_attribute and pg_class it fixes the problem. > > Does a regular VACUUM accomplish the same result? No, a regular vacuum does not seem to make the same difference. > > > But unfortunately vacuuming these table every run is not practical as > > it takes so much time. > > Can you elaborate? VACUUMing *just* the pg_attribute and pg_class tables > takes an excessive amount of time? (You're aware you can VACUUM specific > tables, as well as entire databases, right?) I am aware of this. Time spent vacuuming and performance of these tables is critical. We process around 30,000,000 transnactions a day. We use a batch oriented process that takes these transactions and explodes them into hundreds of normalised tables. This process runs every five minutes. This database also supports a front end reporting system that handles dynamic reporting requests. > > > If I were to create the temp tables without oids, does this mean both > > pg_attribute and pg_class will not be as affected. > > AFAIK, it wouldn't affect either significantly. But it would make a difference.. Right? Thanks for you time Andrew