Thread: INS/UPD/DEL Returning Patch

INS/UPD/DEL Returning Patch

From

"Jonah H. Harris"

Date:

02 March 2006, 16:21:56

All,

This is only the current patch updated to apply cleanly on cvs... it's not ready for -patches yet as I still haven't spent much time looking through it and testing it. This is just for anyone to play with and find issues.

My focus for the next couple days is on getting INS/UPD/DEL RETURNING ready for 8.2... all comments, suggestions, issues would be appreciated.

--
Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
732.331.1324

Attachment

pg82-iudret.patch

Re: INS/UPD/DEL Returning Patch

From

"Jonah H. Harris"

Date:

02 March 2006, 21:19:28

Here's my very first proof-of-concept patch to PL/pgSQL to use the RETURNING syntax... INSERT/UPDATE seem to work fine but I think I've found an error with DELETE RETURNING though, so it doesn't work properly just yet. Give this a test if you get a chance.

CREATE SEQUENCE test_id_seq START 1 INCREMENT 1;

CREATE TABLE test_tbl (
    test_id     BIGINT NOT NULL
                    DEFAULT nextval('test_id_seq'),
    test_name   VARCHAR(64) NOT NULL,
    PRIMARY KEY (test_id));

CREATE OR REPLACE FUNCTION test_func (test_nm VARCHAR)
    RETURNS VOID AS $$
DECLARE
    current_rec RECORD;
BEGIN
    -- Test INSERT RETURNING
    INSERT INTO test_tbl (test_name) VALUES (test_nm)
        RETURNING * INTO current_rec;

    RAISE NOTICE 'test_id is %', current_rec.test_id;
    RAISE NOTICE 'test_name is %', current_rec.test_name;

    -- Test UPDATE RETURNING
    UPDATE test_tbl SET test_name = 'Uncle Bob'
        WHERE test_id = current_rec.test_id
        RETURNING * INTO current_rec;

    RAISE NOTICE 'test_id is %', current_rec.test_id;
    RAISE NOTICE 'test_name is %', current_rec.test_name;

    -- Test DELETE RETURNING
    DELETE FROM test_tbl WHERE test_id = current_rec.test_id
        RETURNING * INTO current_rec;

    -- This DOES NOT WORK
    RAISE NOTICE 'test_id is %', current_rec.test_id;
    RAISE NOTICE 'test_name is %', current_rec.test_name;

    RETURN;
END;
$$ LANGUAGE plpgsql;

On 3/2/06, Jonah H. Harris <jonah.harris@gmail.com> wrote:

All,

This is only the current patch updated to apply cleanly on cvs... it's not ready for -patches yet as I still haven't spent much time looking through it and testing it. This is just for anyone to play with and find issues.

My focus for the next couple days is on getting INS/UPD/DEL RETURNING ready for 8.2... all comments, suggestions, issues would be appreciated.

--
Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
732.331.1324

--
Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
732.331.1324

Attachment

pg82-iudret-plpgsql.patch

Re: INS/UPD/DEL Returning P.tch

From

Alvaro Herrera

Date:

02 March 2006, 21:21:37

Jonah H. Harris wrote:
> All,
> 
> This is only the current patch updated to apply cleanly on cvs... it's not
> ready for -patches yet as I still haven't spent much time looking through it
> and testing it.  This is just for anyone to play with and find issues.

Somebody else already did this in the last few days -- post a patch to
-hackers instead of -patches, claiming that since it's not ready, it's
not suitable for the latter.  I think this is a mistake; a patch is a
patch, whether it's ready for application or not, so it should be posted
to pgsql-patches.  You can of course indicate that the patch is a
work-in-progress and that it shouldn't be applied yet.

Anyway thanks for the patch,

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Re: INS/UPD/DEL Returning P.tch

From

"Jonah H. Harris"

Date:

02 March 2006, 21:23:05

If this is the consensus, then I'm fine with posting to -patches... I just want to make sure people are aware of it so it can get tested. Thanks.

On 3/2/06, Alvaro Herrera <alvherre@commandprompt.com> wrote:

Jonah H. Harris wrote:
> All,
>
> This is only the current patch updated to apply cleanly on cvs... it's not
> ready for -patches yet as I still haven't spent much time looking through it
> and testing it.  This is just for anyone to play with and find issues.

Somebody else already did this in the last few days -- post a patch to
-hackers instead of -patches, claiming that since it's not ready, it's
not suitable for the latter.  I think this is a mistake; a patch is a
patch, whether it's ready for application or not, so it should be posted
to pgsql-patches.  You can of course indicate that the patch is a
work-in-progress and that it shouldn't be applied yet.

Anyway thanks for the patch,

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

--
Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
732.331.1324

Re: INS/UPD/DEL Returning P.tch

From

Neil Conway

Date:

02 March 2006, 21:33:02

On Thu, 2006-03-02 at 17:23 -0500, Jonah H. Harris wrote:
> If this is the consensus, then I'm fine with posting to -patches

Yeah, -patches is the right place.

> I just want to make sure people are aware of it so it can get tested.

I wouldn't expect a whole lot of testing. The usual process is that the
person submitting the patch does all the coding and testing, then
submits it to -patches. When the patch is reviewed, you might need to
resubmit it a few times to satisfy the reviewers' comments. Then a
committer will apply the patch (often with some additional changes, at
least in my case).

-Neil

Installation problem

From

"John"

Date:

02 March 2006, 22:18:17

I got an installation problem. There was an old copy of postgres installed but I want to
install the modified postgres. I get problems related to locating "/usr/local/pgsql/data".

I tried:
[postgres@austin ~]$ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
The files belonging to this database system will be owned by user
"postgres".
This user must also own the server process.

The database cluster will be initialized with locale en_US.UTF-8.
The default database encoding has accordingly been set to UNICODE.

initdb: directory "/usr/local/pgsql/data" exists but is not empty
If you want to create a new database system, either remove or empty
the directory "/usr/local/pgsql/data" or run initdb
with an argument other than "/usr/local/pgsql/data".

Then it does not run correctly after I start the server:
pg_ctl start -w -D /usr/local/pgsql/data

[postgres@austin pgsql]$ psql -U postgres uiuc
Welcome to psql 8.0.3, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

uiuc=# \d
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.
!> \q
[postgres@austin pgsql]$

I realized that it might because I used the old
*/usr/local/pgsql/data/postgresql.conf* and it does not work for the new
one. Then I tried to build a new directory for data, I used following
comments to build a new data directory but it end up in a segmentation
faulty. Do you have any suggestion on it? Thanks a lot.

[postgres@austin ~]$ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/dbdata
The files belonging to this database system will be owned by user
"postgres".
This user must also own the server process.

The database cluster will be initialized with locale en_US.UTF-8.
The default database encoding has accordingly been set to UNICODE.

fixing permissions on existing directory /usr/local/pgsql/dbdata .. ok
creating directory /usr/local/pgsql/dbdata/global ... ok
creating directory /usr/local/pgsql/dbdata/pg_xlog ... ok
creating directory /usr/local/pgsql/dbdata/pg_xlog/archive_status ... ok
creating directory /usr/local/pgsql/dbdata/pg_clog ... ok
creating directory /usr/local/pgsql/dbdata/pg_subtrans ... ok
creating directory /usr/local/pgsql/dbdata/base ... ok
creating directory /usr/local/pgsql/dbdata/base/1 ... ok
creating directory /usr/local/pgsql/dbdata/pg_tblspc ... ok
selecting default max_connections .. 100
selecting default shared_buffers ... 1000
creating configuration files ... ok
creating template1 database in /usr/local/pgsql/dbdata/base/1 .. ok
initializing pg_shadow ... ok
enabling unlimited row size for system tables ... ok
initializing pg_depend ... sh: line 1: 24405 Segmentation fault
"/usr/local/pgsql/bin/postgres" -F -O -c search_path=pg_catalog -c
exit_on_error=true template1 >/dev/null
child process exited with exit code 139
initdb: removing contents of data directory "/usr/local/pgsql/dbdata"

Anyone can give me an idea? Thanks a lot.

Best wishes,
Xiaoqian

Re: Installation problem

From

Tom Lane

Date:

02 March 2006, 23:18:52

"John" <xiaoqianjiang@hotmail.com> writes:
> initializing pg_depend ... sh: line 1: 24405 Segmentation fault=20
> "/usr/local/pgsql/bin/postgres" -F -O -c search_path=3Dpg_catalog -c=20
> exit_on_error=3Dtrue template1 >/dev/null

Seems like you've got a broken postgres executable there.  Where did you
get it from?

(BTW, PG 8.0.3 is a bit behind the times: as long as you are doing a fresh
install, there is no good reason not to be using 8.0.7.  Or maybe even
8.1.something.)
        regards, tom lane