Thread: Tranactions and viewing updated data

Postgresql 7.4.5 On RedHat 8

Is there a way to see what is happening with a process?

We have a 4gl module that maintains our database (ie creates the tables, adds columns etc) - the problem (?) is that when it runs there is no 'visibility' of what's going on - for example  I am just updating a 400,000 row table - the table has had 3 new columns appended to it - two of the three columns need to be NOT NULL - so the the process does:

ALTER TABLE blah ADD COLUMN blah ....;

UPDATE blah
     SET blah = 'blah' .....

ALTER TABLE blah ALTER COLUMN blah SET NOT NULL;

I can see from a ps statement that there is an UPDATE statement running, but is there anyway of seeing how far through the update it is? I assume that I can't see the data until the whole 'implied' transaction is complete (ie all 400,000 rows updated) - is there a

The process has been running for 60 minutes and I don't want to kill it if it's nearly complete (I have just realised that the update triggers on the table weren't disabled before the run - so that is slowing it down)

Steve Tucknott <steve@retsol.co.uk> writes:
> I can see from a ps statement that there is an UPDATE statement running,
> but is there anyway of seeing how far through the update it is?

You could use contrib/pgstattuple to determine how many dead tuples
there are in the table.  It looks to me like pgstattuple will count rows
emitted by a still-in-progress UPDATE as dead rows, so what you want to
check is how fast that number is rising.  (This is relatively expensive,
since pgstattuple has to scan the whole table for itself, but if you're
trying to figure out whether you might end up waiting for weeks, it's
worth it ...)

            regards, tom lane

CREATE OR REPLACE FUNCTION test_temp_table() RETURNS void AS $$
BEGIN
CREATE TABLE temp_table AS SELECT * FROM objects;
PERFORM * FROM temp_table;  -- without this line no problems
DROP TABLE temp_table;
RETURN;
END
$$ LANGUAGE plpgsql;

SELECT test_temp_table();
SELECT test_temp_table();


Why can't I execute this function twice in the same database connection?

I get the following error:

ERROR:  relation with OID 169873 does not exist
CONTEXT:  SQL statement "SELECT  * FROM temp_table"
PL/pgSQL function "test_temp_table" line 4 at perform

On Fri, 2005-10-07 at 13:11 +0200, Tjibbe wrote:
> CREATE OR REPLACE FUNCTION test_temp_table() RETURNS void AS $$
> BEGIN
> CREATE TABLE temp_table AS SELECT * FROM objects;
> PERFORM * FROM temp_table;  -- without this line no problems
> DROP TABLE temp_table;
> RETURN;
> END
> $$ LANGUAGE plpgsql;
>
> SELECT test_temp_table();
> SELECT test_temp_table();
>
>
> Why can't I execute this function twice in the same database connection?
>
> I get the following error:
>
> ERROR:  relation with OID 169873 does not exist
> CONTEXT:  SQL statement "SELECT  * FROM temp_table"
> PL/pgSQL function "test_temp_table" line 4 at perform

The second time around, the function is cached and tries to use the oid
from the first time.  Since the table has been deleted and recreated,
the oid is different.

You need to do
   EXECUTE 'SELECT * FROM temp_table';
which will re-evaluate the command each time it is run.

Oliver Elphick

Hi, I have a similar case
but instead of the statement

PERFORM * FROM temp_table;  -- without this line no problems

I have a SELECT INTO inside the plpgsql function

SELECT * INTO rec FROM temp_table;

That return the same error as Tjibbe got.
I try to use something like

EXEC 'SELECT * INTO rec FROM temp_table';

But I now postgres (8.1.1) returns a error

EXEC of SELECT ... INTO is not implemented yet.

There are another aproach to make the expression re-evalueted ?

Thank's in advance

Oliver Elphick wrote:

On Fri, 2005-10-07 at 13:11 +0200, Tjibbe wrote: 

CREATE OR REPLACE FUNCTION test_temp_table() RETURNS void AS $$
BEGIN
CREATE TABLE temp_table AS SELECT * FROM objects;
PERFORM * FROM temp_table;  -- without this line no problems
DROP TABLE temp_table;
RETURN;
END
$$ LANGUAGE plpgsql;

SELECT test_temp_table();
SELECT test_temp_table(); 


Why can't I execute this function twice in the same database connection?

I get the following error:

ERROR:  relation with OID 169873 does not exist
CONTEXT:  SQL statement "SELECT  * FROM temp_table"
PL/pgSQL function "test_temp_table" line 4 at perform   

The second time around, the function is cached and tries to use the oid
from the first time.  Since the table has been deleted and recreated,
the oid is different.

You need to do  EXECUTE 'SELECT * FROM temp_table';
which will re-evaluate the command each time it is run.

Oliver Elphick


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

 

On Wed, 2005-12-14 at 17:05 -0200, Luiz K. Matsumura wrote:
> Hi, I have a similar case
> but instead of the statement
> PERFORM * FROM temp_table;  -- without this line no problems
> I have a SELECT INTO inside the plpgsql function
> SELECT * INTO rec FROM temp_table;
> That return the same error as Tjibbe got.
> I try to use something like
> EXEC 'SELECT * INTO rec FROM temp_table';
> But I now postgres (8.1.1) returns a error
>
> EXEC of SELECT ... INTO is not implemented yet.

You have the syntax wrong.  It is:

   EXECUTE command-string [ INTO target ];

In your case, that is:

   EXECUTE 'SELECT * FROM temp_table' INTO rec;

so that the INTO phrase is outside the string.

--
Oliver Elphick                                          olly@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
                 ========================================
   Do you want to know God?   http://www.lfix.co.uk/knowing_god.html

Oh my god,
It's so simple and I'm spend a lot of time in this problem.
And the bad news for me is that this was all the time in the Postgres Document.
Thank very much Oliver. :-)

Altought this, I want to sugest to add a session in Pl/pgSQL documentation,  a "command reference"
like the SQL commands reference with brief description of the sintax and a link to the
proper location in atual document section for a detailed behavior.
I say this, because I think sometimes are hard to find the desired information in the doc.

Thanks a lot.


Oliver Elphick wrote:

On Wed, 2005-12-14 at 17:05 -0200, Luiz K. Matsumura wrote: 

Hi, I have a similar case
but instead of the statement
PERFORM * FROM temp_table;  -- without this line no problems
I have a SELECT INTO inside the plpgsql function
SELECT * INTO rec FROM temp_table;
That return the same error as Tjibbe got.
I try to use something like
EXEC 'SELECT * INTO rec FROM temp_table';
But I now postgres (8.1.1) returns a error 

EXEC of SELECT ... INTO is not implemented yet.   

You have the syntax wrong.  It is:
  EXECUTE command-string [ INTO target ];

In your case, that is:
  EXECUTE 'SELECT * FROM temp_table' INTO rec;

so that the INTO phrase is outside the string.