[GENERAL] databse version
Hello, I'm creating a database and I have got a table with a "version" field. Can I update on structure changes (DDL) like create / update table increment this field automatically? I would like to create a versionizing for my database which counts the changes. IMHO I need a trigger, which is run if a DDL statement is fired on the database Thanks Phil -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Question on explain
Il 10/06/2013 04:19, Jeff Janes ha scritto: On further thought, that is not strange at all. You have two very selective join conditions, and the planner assumes they are independent, so that it can multiply the selectivities. But in reality they are completely (or almost completely) dependent. If the planner knew about cross column correlations, that might not even help as you can have complete statistical dependence without having correlation. It seems unlikely to me that the timestamp belongs in both tables, since it's value seems to be completely dependent on the value of the UUID. In any event, it is unlikely the planner would pick a different plan were it to correctly understand the selectivities, so no harm is done. Although it is easy to imagine similar queries where that would not be the case. Cheers, Jeff Thank you very much ;) Enrico -- That's one small step for man; one giant leap for mankind Enrico Pirozzi Tel. +39 0861 1855771 - Mob.+39 328 4164437 - Fax +39 0861 1850310 http://www.pgtraining.com - i...@pgtraining.com www.enricopirozzi.info - i...@enricopirozzi.info Skype sscotty71 - Gtalk sscott...@gmail.com smime.p7s Description: Firma crittografica S/MIME
Re: [GENERAL] My function run successfully with cursor, but can't change table
高健 wrote: > CREATE OR REPLACE Function ... > BEGIN > BEGIN > UPDATE ... > COMMIT; > EXCEPTION > WHEN OTHERS THEN > END; > END; > But when I select the table again, I found it not changed. > Anybody know the reason, maybe there are some wrong way by > which I use the cursor. It has nothing to do with the way you are using the cursor; your problem is that you are causing an error by attempting to COMMIT inside a function (which is not allowed). This rolls back the subtransaction defined by the BEGIN/EXCEPTION block. You then suppress any display of the error with the WHEN OTHERS block. -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgresql - Currval Vs Session Pool
Gustavo Amarilla Santacruz wrote: > In the PostgreSQL documentation I found "currval: Return the value most > recently obtained by nextval > for this sequence in the current session " > > In other documentations (pgpool, for example), I found "Connection Pooling > pgpool-II saves connections to the PostgreSQL servers, and reuse them > whenever a new connection with > the same properties (i.e. username, database, protocol version) comes in. It > reduces connection > overhead, and improves system's overall throughput" > > Then, I have the following question: PostgreSQL differentiates between > sessions created for the same > user? > > Background > == > - I, traditionalmente, have several user in a web application (user table, > for example); but I use > only one postgresql-db-user to get connetions to database > > - If a CREATE (CRUD) operation uses a head-detail couple of tables, then in a > transaction: data in > HEAD table is inserted; next, the value for the primary key is achieved from > currval function; next > references to head table is inserted in detail table. "currval" will return a different value or an error message if the query happens to use a different session than the one that you used for "nextval". The best way to solve this is the INSERT ... RETURNING statement, like in INSERT INTO t VALUES (...) RETURNING id, which will return new value of the automatically generated column. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trouble with replication
From: Michael Paquier [mailto:michael.paqu...@gmail.com] Sent: Thursday, June 06, 2013 7:01 PM To: David Greco Cc: John R Pierce; pgsql-general@postgresql.org Subject: Re: [GENERAL] Trouble with replication On Thu, Jun 6, 2013 at 9:19 PM, David Greco mailto:david_gr...@harte-hanks.com>> wrote: Then what is the purpose to shipping the archived WAL files to the slave? i.e. if wal_keep_segments has to be high enough to cover any replication lag anyways, then should I even bother shipping them over? Oh. I just noticed that you set up restore_command on slave, so if streaming replication failed due to a WAL file already removed on master, slave process will try to fetch missing WAL files from the archive. Could you provide more logs of slave? Are you sure that the missing WAL file was not fetched from the archive after failing to get it through streaming replication? The errors continued ad infinitum on the slave. I've since redone the replication setup with keep WAL segments set on the master to a rather large number, enough to nearly fill the drive dedicated to XLOG. Replication appears to be working properly now. Best I can figure, it had something to do with the pg_restore used to populate the master? This would be a large 30GB transaction.
Re: [GENERAL] Postgresql - Currval Vs Session Pool
On Sun, Jun 9, 2013 at 10:59 AM, Gustavo Amarilla Santacruz wrote: > Hello, all. > > In the PostgreSQL documentation I found "currval: Return the value most > recently obtained by nextval for this sequence in the current session " > > In other documentations (pgpool, for example), I found "Connection Pooling > pgpool-II saves connections to the PostgreSQL servers, and reuse them > whenever a new connection with the same properties (i.e. username, database, > protocol version) comes in. It reduces connection overhead, and improves > system's overall throughput" > > Then, I have the following question: PostgreSQL differentiates between > sessions created for the same user? Connection pooling means you have to carefully consider using feature of the database that is scoped to the session. This includes currval(), prepared statements, listen/notify, advisory locks, 3rd party libraries that utilize backend private memory, etc. For currval(), one solution is to only use those features 'in-transaction', and make sure your pooler is fully transaction aware -- pgbouncer does this and I think (but I'm not sure) that pgpool does as well. Another solution is to stop using currval() and cache the value on the client side. postgres 8.2 RETURNING facilities this: INSERT INTO foo (...) RETURNING foo_id; This is a better way to deal with basis CRUD -- it also works for all default values, not just sequences. The only time I use currval() etc any more is inside server side functions. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgresql - Currval Vs Session Pool
Thank you very much, Laurenz Albe. On Mon, Jun 10, 2013 at 9:21 AM, Albe Laurenz wrote: > Gustavo Amarilla Santacruz wrote: > > In the PostgreSQL documentation I found "currval: Return the value most > recently obtained by nextval > > for this sequence in the current session " > > > > In other documentations (pgpool, for example), I found "Connection > Pooling > > pgpool-II saves connections to the PostgreSQL servers, and reuse them > whenever a new connection with > > the same properties (i.e. username, database, protocol version) comes > in. It reduces connection > > overhead, and improves system's overall throughput" > > > > Then, I have the following question: PostgreSQL differentiates between > sessions created for the same > > user? > > > > Background > > == > > - I, traditionalmente, have several user in a web application (user > table, for example); but I use > > only one postgresql-db-user to get connetions to database > > > > - If a CREATE (CRUD) operation uses a head-detail couple of tables, then > in a transaction: data in > > HEAD table is inserted; next, the value for the primary key is achieved > from currval function; next > > references to head table is inserted in detail table. > > "currval" will return a different value or an error message if > the query happens to use a different session than the one that > you used for "nextval". > > The best way to solve this is the INSERT ... RETURNING statement, > like in INSERT INTO t VALUES (...) RETURNING id, which will > return new value of the automatically generated column. > > Yours, > Laurenz Albe > -- Gustavo Amarilla
Re: [GENERAL] Postgresql - Currval Vs Session Pool
Thank you, Merlin Moncure. On Mon, Jun 10, 2013 at 10:13 AM, Merlin Moncure wrote: > On Sun, Jun 9, 2013 at 10:59 AM, Gustavo Amarilla Santacruz > wrote: > > Hello, all. > > > > In the PostgreSQL documentation I found "currval: Return the value most > > recently obtained by nextval for this sequence in the current session > " > > > > In other documentations (pgpool, for example), I found "Connection > Pooling > > pgpool-II saves connections to the PostgreSQL servers, and reuse them > > whenever a new connection with the same properties (i.e. username, > database, > > protocol version) comes in. It reduces connection overhead, and improves > > system's overall throughput" > > > > Then, I have the following question: PostgreSQL differentiates between > > sessions created for the same user? > > Connection pooling means you have to carefully consider using feature > of the database that is scoped to the session. This includes > currval(), prepared statements, listen/notify, advisory locks, 3rd > party libraries that utilize backend private memory, etc. > > For currval(), one solution is to only use those features > 'in-transaction', and make sure your pooler is fully transaction aware > -- pgbouncer does this and I think (but I'm not sure) that pgpool does > as well. > > Another solution is to stop using currval() and cache the value on the > client side. postgres 8.2 RETURNING facilities this: > > INSERT INTO foo (...) RETURNING foo_id; > > This is a better way to deal with basis CRUD -- it also works for all > default values, not just sequences. The only time I use currval() etc > any more is inside server side functions. > > merlin > -- Gustavo Amarilla
Re: [GENERAL] Postgresql - Currval Vs Session Pool
On Mon, Jun 10, 2013 at 11:24 AM, Gustavo Amarilla Santacruz < gusama...@gmail.com> wrote: > Thank you, Merlin Moncure. > > > On Mon, Jun 10, 2013 at 10:13 AM, Merlin Moncure wrote: > >> On Sun, Jun 9, 2013 at 10:59 AM, Gustavo Amarilla Santacruz >> wrote: >> > Hello, all. >> > >> > In the PostgreSQL documentation I found "currval: Return the value most >> > recently obtained by nextval for this sequence in the current session >> " >> > >> > In other documentations (pgpool, for example), I found "Connection >> Pooling >> > pgpool-II saves connections to the PostgreSQL servers, and reuse them >> > whenever a new connection with the same properties (i.e. username, >> database, >> > protocol version) comes in. It reduces connection overhead, and improves >> > system's overall throughput" >> > >> > Then, I have the following question: PostgreSQL differentiates between >> > sessions created for the same user? >> >> Connection pooling means you have to carefully consider using feature >> of the database that is scoped to the session. This includes >> currval(), prepared statements, listen/notify, advisory locks, 3rd >> party libraries that utilize backend private memory, etc. >> >> For currval(), one solution is to only use those features >> 'in-transaction', and make sure your pooler is fully transaction aware >> -- pgbouncer does this and I think (but I'm not sure) that pgpool does >> as well. >> >> Another solution is to stop using currval() and cache the value on the >> client side. postgres 8.2 RETURNING facilities this: >> >> INSERT INTO foo (...) RETURNING foo_id; >> >> This is a better way to deal with basis CRUD -- it also works for all >> default values, not just sequences. The only time I use currval() etc >> any more is inside server side functions. >> >> merlin >> > > > > -- > > Gustavo Amarilla > > I tested the following function for a table; it works: CREATE OR REPLACE FUNCTION returning_test( p_name TEXT ) RETURNS INT AS $$ DECLARE v_code INT; BEGIN -- HEAD table definition: -- == -- -- CREATE TABLE head( -- code SERIAL PRIMARY KEY , -- name TEXT UNIQUE NOT NULL -- ); -- INSERT INTO head( head_name ) VALUES( p_name ) RETURNING code INTO v_code; RETURN( v_code ); END; $$ LANGUAGE plpgsql; Gustavo Amarilla
Re: [GENERAL] My function run successfully with cursor, but can't change table
Kevin Grittner wrote on 10.06.2013 15:19: It has nothing to do with the way you are using the cursor; your problem is that you are causing an error by attempting to COMMIT inside a function (which is not allowed). This rolls back the subtransaction defined by the BEGIN/EXCEPTION block. You then suppress any display of the error with the WHEN OTHERS block. I thought you could *never* use commit (or rollback) inside a function? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] My function run successfully with cursor, but can't change table
Thomas Kellerer wrote: > Kevin Grittner wrote on 10.06.2013 15:19: > >It has nothing to do with the way you are using the cursor; your > >problem is that you are causing an error by attempting to COMMIT > >inside a function (which is not allowed). This rolls back the > >subtransaction defined by the BEGIN/EXCEPTION block. You then > >suppress any display of the error with the WHEN OTHERS block. > > I thought you could *never* use commit (or rollback) inside a function? You cannot use transaction commands directly, but EXCEPTION blocks use savepoints internally. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] why does this not give a syntax error?
pg 9.2: delete from "ExternalDocument" where id = 11825657and "Billed" = 'f'; -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] why does this not give a syntax error?
Scott Ribe writes: > pg 9.2: > delete from "ExternalDocument" where id = 11825657and "Billed" = 'f'; "11825657and" is not any more lexically ambiguous than "11825657+". It has to be two separate tokens, and that's how it's read. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] why does this not give a syntax error?
Scott Ribe writes: > On Jun 10, 2013, at 12:52 PM, Tom Lane wrote: >> "11825657and" is not any more lexically ambiguous than "11825657+". >> It has to be two separate tokens, and that's how it's read. > But it's not read correctly. [ shrug... ] Works for me. You want to put together a self-contained test case showing differently? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] why does this not give a syntax error?
On Jun 10, 2013, at 12:52 PM, Tom Lane wrote: > Scott Ribe writes: >> pg 9.2: >> delete from "ExternalDocument" where id = 11825657and "Billed" = 'f'; > > "11825657and" is not any more lexically ambiguous than "11825657+". > It has to be two separate tokens, and that's how it's read. But it's not read correctly. In other words: delete from "ExternalDocument" where id = 11825657and "Billed" = 'f'; deleted 0 rows, while: delete from "ExternalDocument" where id = 11825657 and "Billed" = 'f'; deleted 1 row. ??? -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] updatable view
Hi, I am using 9.2. The documentation says that there is no support for updatable view. It suggesting to use the triggers or rule. Can somebody paste the sample code for the same. Thanks, Sajeev
Re: [GENERAL] databse version
On 06/10/2013 12:52 AM, Philipp Kraus wrote: Hello, I'm creating a database and I have got a table with a "version" field. Can I update on structure changes (DDL) like create / update table increment this field automatically? I would like to create a versionizing for my database which counts the changes. IMHO I need a trigger, which is run if a DDL statement is fired on the database In the current beta 9.3 there are event triggers that would seem to be what you are looking for: http://www.postgresql.org/docs/9.3/static/event-triggers.html Thanks Phil -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] databse version
On 6/10/2013 12:52 AM, Philipp Kraus wrote:
I'm creating a database and I have got a table with a "version" field.
Can I update on structure changes (DDL) like create / update table
increment this field automatically?
I would like to create a versionizing for my database which counts the
changes.
IMHO I need a trigger, which is run if a DDL statement is fired on the
database
so if someone runs a script that does a half dozen create/alters, you
want to bump your version that many times?
we handle our version control by not allowing anyone to make direct
changes, instead all changes need to be made with .sql scripts, of which
we maintain two sets, one to create a new schema version x.y, and the
other to update x.y-1 to x.y. and yes, part of these scripts stores
the version in a configuration table of (key,value) pairs, like
('version','x.y')
--
john r pierce 37N 122W
somewhere on the middle of the left coast
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] INSERT RETURNING with values other than inserted ones.
Hello,
I want to insert new values into target table 'a' from source table 'b', and
then update table 'b' with ids from table 'a', somewhat like:
CREATE TABLE a(id SERIAL, name TEXT);
INSERT INTO a (name) VALUES('Jason');
INSERT INTO a (name) VALUES('Peter');
CREATE TABLE b(row_id serial, id INT, name TEXT);
INSERT INTO b (name) VALUES('Jason');
INSERT INTO b (name) VALUES('Peter');
WITH inserted AS (INSERT INTO a (name) SELECT b.name FROM b WHERE b.name = name
RETURNING a.id)
UPDATE b SET id = inserted.id FROM inserted WHERE inserted.row_id = b.row_id;
However this would not work for obvious reason:
WHERE inserted.row_id = b.row_id is invalid because RETURNING clause cannot
return row_id.
What can be returned are only columns of 'a', but they are insufficient to
identify matching records of 'b'.
So the question is - what to put in WHERE clause to match RETURNING with rows
being inserted from 'b'?
Thanks!
--
Aleksandr Furmanov
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INSERT RETURNING with values other than inserted ones.
If you're just replicating the data from table A into table B, why does it need
its own ID number? Wouldn't the table A ID suffice?
I'd recommend using the following:
CREATE TABLE b AS ( SELECT * FROM a );
This way, you only define the columns and insert the data once, then let
Postgres do the rest for you. Obviously if you need to have a separate table B
ID, you can alter as necessary.
Good luck,
Richard Dunks
On Jun 10, 2013, at 7:29 PM, Aleksandr Furmanov
wrote:
> Hello,
> I want to insert new values into target table 'a' from source table 'b', and
> then update table 'b' with ids from table 'a', somewhat like:
>
> CREATE TABLE a(id SERIAL, name TEXT);
> INSERT INTO a (name) VALUES('Jason');
> INSERT INTO a (name) VALUES('Peter');
>
> CREATE TABLE b(row_id serial, id INT, name TEXT);
> INSERT INTO b (name) VALUES('Jason');
> INSERT INTO b (name) VALUES('Peter');
>
>
> WITH inserted AS (INSERT INTO a (name) SELECT b.name FROM b WHERE b.name =
> name RETURNING a.id)
> UPDATE b SET id = inserted.id FROM inserted WHERE inserted.row_id = b.row_id;
>
> However this would not work for obvious reason:
>
> WHERE inserted.row_id = b.row_id is invalid because RETURNING clause cannot
> return row_id.
> What can be returned are only columns of 'a', but they are insufficient to
> identify matching records of 'b'.
>
> So the question is - what to put in WHERE clause to match RETURNING with rows
> being inserted from 'b'?
>
> Thanks!
>
> --
> Aleksandr Furmanov
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] INSERT RETURNING with values other than inserted ones.
Thanks,
However I am not just replicating data from 'a' to 'b', I provided simplified
example, in reality table 'b' keeps data which are going to be merged into 'a',
some rows will be updated, some added. There is some other work has to be done
on 'b' before merging into 'a' and that work relies on 'id' from a.
On Jun 10, 2013, at 4:39 PM, Richard Dunks wrote:
> If you're just replicating the data from table A into table B, why does it
> need its own ID number? Wouldn't the table A ID suffice?
>
> I'd recommend using the following:
>
> CREATE TABLE b AS ( SELECT * FROM a );
>
> This way, you only define the columns and insert the data once, then let
> Postgres do the rest for you. Obviously if you need to have a separate table
> B ID, you can alter as necessary.
>
> Good luck,
> Richard Dunks
>
> On Jun 10, 2013, at 7:29 PM, Aleksandr Furmanov
> wrote:
>
>> Hello,
>> I want to insert new values into target table 'a' from source table 'b', and
>> then update table 'b' with ids from table 'a', somewhat like:
>>
>> CREATE TABLE a(id SERIAL, name TEXT);
>> INSERT INTO a (name) VALUES('Jason');
>> INSERT INTO a (name) VALUES('Peter');
>>
>> CREATE TABLE b(row_id serial, id INT, name TEXT);
>> INSERT INTO b (name) VALUES('Jason');
>> INSERT INTO b (name) VALUES('Peter');
>>
>>
>> WITH inserted AS (INSERT INTO a (name) SELECT b.name FROM b WHERE b.name =
>> name RETURNING a.id)
>> UPDATE b SET id = inserted.id FROM inserted WHERE inserted.row_id = b.row_id;
>>
>> However this would not work for obvious reason:
>>
>> WHERE inserted.row_id = b.row_id is invalid because RETURNING clause cannot
>> return row_id.
>> What can be returned are only columns of 'a', but they are insufficient to
>> identify matching records of 'b'.
>>
>> So the question is - what to put in WHERE clause to match RETURNING with
>> rows being inserted from 'b'?
>>
>> Thanks!
>>
>> --
>> Aleksandr Furmanov
>>
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] databse version
We use this system for versioning: http://pyrseas.wordpress.com/2011/02/18/sql-database-version-control-%E2%80%93-depesz-versioning/ Works like a champ. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] updatable view
On Tue, Jun 11, 2013 at 3:32 AM, Sajeev Mayandi wrote: > Hi, > > I am using 9.2. The documentation says that there is no support for > updatable view. It suggesting to use the triggers or rule. Can somebody > paste the sample code for the same. > > Very well summarized by Craig on SO with links (code/implementation). Below link helps you about "how to" on the updatable views. http://stackoverflow.com/questions/13151566/cannot-update-view --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
[GENERAL] Oracle Packages and Stored Procedures - PostgreSQL conversion
Hi, I have some Oracle Packages and some standalone procedure in Oracle schema. As we are converting our application as PostgreSQL compliant we are looking at these Oracle objects. These packages have no variables declared and consist of a few stored procedures. Please suggest a good approach to convert them into PosgreSQL compliant objects 1. PL/SQL PackagesIs it advisable to convert constituent stored procedures into PostgreSQL Functions and wrap them inside another function? 2. PL/SQL Stored ProceduresIs it advisable to convert stored procedures into PostgreSQL Functions? I am quite new to PostgreSQL; hence seeking some help from larger group of experts. Currently I try to understand the suggestions from 9.2.4 documentation here: http://www.postgresql.org/docs/9.2/static/plpgsql-porting.html Thanks in advance,Panneerselvam Posangu
Re: [GENERAL] Oracle Packages and Stored Procedures - PostgreSQL conversion
Hello 2013/6/11 Panneerselvam Posangu : > Hi, > > I have some Oracle Packages and some standalone procedure in Oracle schema. > As we are converting our application as PostgreSQL compliant we are looking > at these Oracle objects. These packages have no variables declared and > consist of a few stored procedures. Please suggest a good approach to > convert them into PosgreSQL compliant objects If you don't use a global objects, then you can use a schema instead package look to orafce as example this technique https://github.com/orafce/orafce > > 1. PL/SQL Packages > Is it advisable to convert constituent stored procedures into PostgreSQL > Functions and wrap them inside another function? > > 2. PL/SQL Stored Procedures > Is it advisable to convert stored procedures into PostgreSQL Functions? There are no more possibilities. Depends on what you do, but usually almost code is portable to PostgreSQL. Some parts should be rewritten - for example Pg has different design of custom aggregates, there are no collections (use arrays instead) or autonomous transactions, ... but almost all functionality is available. > > I am quite new to PostgreSQL; hence seeking some help from larger group of > experts. Currently I try to understand the suggestions from 9.2.4 > documentation here: > http://www.postgresql.org/docs/9.2/static/plpgsql-porting.html > read well documentation first, look on http://postgres.cz/wiki/PL/pgSQL_%28en%29 and be creative :) Regards Pavel Stehule > Thanks in advance, > Panneerselvam Posangu -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PSA: If you are running Precise/12.04 upgrade your kernel.
Perhaps someone with a spare server floating around could install Ubuntu LTS and run some pg-bench benchmarks with the various kernel options? Like you, I'd have to stick to official updates for production systems. -Toby On 07/06/13 15:36, Nikhil G Daddikar wrote: Folks, This is bad news as I run Ubuntu 12.04 LTS. However, my ubuntu 12.04 LTS boxes have been updated to "3.5.0-32-generic" (official update). Any idea whether the Postgresql has problems with this kernel? I'd like to follow the "official" LTS updates because I am not sure what other surprises I could face if I move to an unofficial one. Thanks! Nikhil On 07-06-2013 04:18, Scott Marlowe wrote: On Thu, Jun 6, 2013 at 4:35 PM, Joshua D. Drake wrote: Hello, I had the distinct displeasure of staying up entirely too late with a customer this week because they upgraded to 12.04 and immediately experienced a huge performance regression. In the process they also upgraded to PostgreSQL 9.1 from 8.4. There were a lot of knobs to change/fix/modify because of this. However, nothing I did fixed the problem. Until... I upgraded the kernel. Upgrading from 3.2Precise to the 3.9.4 kernel produced the following results: I've since heard that 3.4 also fixes this issue as well. What are you using for your IO on these boxes? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
