Re: [GENERAL] [pgadmin-support] Help for Migration
On 12/07/2011 03:23 PM, mamatha_kagathi_c...@dell.com wrote:
Hi Alban/Craig,
Employeedetailinsert is procedure I have created in PostgreSQL. When I try to
execute the procedure directly in Pgadmin as
EXEC Employeedetailinsert (parameters same as below) It works fine with desired
result.
PgAdmin uses libpq directly. It must be translating the `EXEC' into
something PostgreSQL can understand, because the psql command line tool
(which also uses libpq) doesn't know what EXEC means.
regress= EXEC dummyfunction();
ERROR: syntax error at or near EXEC
LINE 1: EXEC dummyfunction();
But When I call the procedure in Classic ASP I get the below error. I do not
understand why the driver assumes it as function in the 1st place. I am using
Postgres Native driver .
Since you're talking about ODBC, I presume you're *actually* using
PsqlODBC as your database driver. AFAIK there's no such thing as
postgres native driver.
In the call when I am using CALL as suggested below
OK, and since you're using ODBC the CALL gets translated to a
server-side proc invocation, that should be no problem.
You're not actually showing your ODBC code or ODBC query string, which
would be helpful.
Executing Procedure =EXEC employeedetailinsert( '
123','55','Mamatha','Chandrashekar','06','05','9886269427','mamatha...@dell.com','12/10/2010','','7','Active','','Bangalore','IG','906','Muralikrishna','TG-,'TPDBA01','TPDBA01-
DBA Practice','No','','')
PostgreSQL Native Provider error '80040e14'
ERROR: function employeedetailinsert(unknown, unknown, unknown, unknown,
unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown,
unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown,
unknown, unknown, unknown) does not exist LINE 1: SELECT * FROM
employeedetailinsert(' 123','55','Mamat... ^ HINT: No function matches the
given name and argument types. You might need to add explicit type casts.
Possibilities:
- You might have defined your function with a double-quoted name, eg
Employeedetailinsert. This makes it case sensitive. If that's the
case, you'll see it with a capital letter when you run the \df command
in psql or browse functions in PgAdmin;
- You've mucked up your argument list and it isn't really the same
length as what you used in PgAdmin after all or you've got the wrong
data types;
- You might need to specify explicit data types for your parameters in
the ODBC call, eg 'Active'::text . Please show your function definition
(at least the line with CREATE OR REPLACE FUNCTION ( parameters )
RETURNS ... on it) so we can see what you're actually trying to call;
- You've set a search_path that means that your ODBC call can't find the
function because it's in a schema that isn't being searched;
- ?
Please show your function definition.
--
Craig Ringer
--
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 DBA in SPAAAAAAAACE
lol! Στις Tuesday 06 December 2011 20:02:40 ο/η Bèrto ëd Sèra έγραψε: +1 say hello to Laika, if she's still there :) Bèrto On 6 December 2011 20:33, Torello Querci tque...@gmail.com wrote: 2011/12/6 Merlin Moncure mmonc...@gmail.com: On Tue, Dec 6, 2011 at 10:56 AM, Joe Miller joe.d.mil...@gmail.com wrote: You may have seen this, but RedGate software is sponsoring a contest to send a DBA on a suborbital space flight. And there is a PostgreSQL representativeme! https://www.dbainspace.com/finalists/joe-miller Voting is open for 7 days. Don't let one of those Oracle or SQL Server punks win :p so jealous -- I didn't make the cut. Well, you'll have my vote. me too :) merlin -- 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 -- Achilleas Mantzios -- 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] Installing different PostgreSQL versions in parallel
Hi John On Tue, Dec 06, 2011 at 10:55:40PM -0800, John R Pierce wrote: afraid I don't have much int he way of suggestions for you to achieve that goal.I install my Solaris postgres builds into /opt/(mygroup)/pgsqlXX/ and their lib and bins and everything are in that path, then when I launch them, I expressly put the data somewhere like /u02/pgsqlXX/data Thanks a lot for your time and effort. One solution for my problem would be adjusting the SONAME of the libraries. At least for 8.X libraries, it is set to 'libname.so.M'. I could adjust the SONAME to include the minor version as well, like 'libname.so.M.N', which would allow me to have libraries of different pgsql versions in /opt/csw/lib and make sure the binaries load the libraries matching their version. However, there is a concern in the OpenCSW community against this approach. Therefore, I would like to know what pgsql thinks about mixing binaries and libraries of different pgsql versions. Maybe a developer could shed some light on this issue? Regards Rafael -- 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] [pgadmin-support] Help for Migration
On 7 December 2011 10:08, mamatha_kagathi_c...@dell.com wrote: The procedure definition is CREATE OR REPLACE PROCEDURE -- So I am not calling a function but a procedure. I don't think CREATE PROCEDURE is actually a valid command in Postgres. The 9.0 documentation seems to confirm that (http://www.postgresql.org/docs/9.0/static/sql-commands.html). In Postgres, procedures are void-returning functions - there's no difference. It's just a naming convention. Perhaps you're using some 3rd party code for MS-SQL compatibility? I imagine CREATE PROCEDURE would then be a simple wrapper around CREATE FUNCTION ... RETURNING void. Heh, didn't know you could define DEFAULT argument values like that, but it seems you can! -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Queries take long long(10 sec.) time running
Hi, I have a vertical database schema running with an webapp. I query this database in the webapp rotating the vertical schema with a view(something like a pivot view) I know that this type of operation is very expensive, but I have the webapp running with acceptable response time for 45 days(machine uptime). Now the queries started to take much much time to execute. I use a VPS with 512MB memory, and I have Apache2, PostgreSQL 8.4, MySQL 5. The webapp don't have high traffic, have about 700 visits per day... I've run top and I see that the server is using a lot of swap. I have ordered the top to give me the used swap, the result is this: [code] top - 09:24:13 up 49 days, 22:44, 3 users, load average: 0.15, 0.12, 0.13 Tasks: 123 total, 1 running, 122 sleeping, 0 stopped, 0 zombie Cpu(s): 0.0%us, 0.0%sy, 0.0%ni, 96.6%id, 3.3%wa, 0.0%hi, 0.0%si, 0.0%st Mem:500452k total, 490576k used, 9876k free, 496k buffers Swap: 524284k total, 343664k used, 180620k free,32428k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ SWAP COMMAND 1881 mysql 20 0 929m 20240 S 0.0 0.4 142:11.73 927m mysqld 11093 root 20 0 275m 560 S 0.0 0.0 0:03.46 275m httpd 19462 apache20 0 280m 6704 1796 S 0.0 1.3 0:02.00 273m httpd 11103 apache20 0 280m 7076 1740 S 0.0 1.4 0:07.80 273m httpd 11095 apache20 0 280m 7544 1788 S 0.0 1.5 0:08.70 273m httpd 11260 apache20 0 280m 7548 1548 S 0.0 1.5 0:07.94 273m httpd 11096 apache20 0 280m 7728 1588 S 0.0 1.5 0:08.45 272m httpd 12668 apache20 0 280m 7660 1804 S 0.0 1.5 0:07.30 272m httpd 11293 apache20 0 280m 7908 1800 S 0.0 1.6 0:08.59 272m httpd 12669 apache20 0 280m 8052 1860 S 0.0 1.6 0:07.69 272m httpd 11099 apache20 0 277m 4940 1720 S 0.0 1.0 0:08.64 272m httpd 11297 apache20 0 280m 8100 1884 S 0.0 1.6 0:09.00 272m httpd 11102 apache20 0 280m 8148 1920 S 0.0 1.6 0:09.34 272m httpd 12672 apache20 0 280m 8172 1804 S 0.0 1.6 0:07.62 272m httpd 12113 apache20 0 280m 8220 1804 S 0.0 1.6 0:07.87 272m httpd 11100 apache20 0 280m 8348 1940 S 0.0 1.7 0:08.70 272m httpd 12663 apache20 0 278m 7188 1940 S 0.0 1.4 0:07.66 271m httpd 19350 apache20 0 277m 6148 1936 S 0.0 1.2 0:07.23 271m httpd 11105 apache20 0 280m 8928 2412 S 0.0 1.8 0:08.08 271m httpd 1960 apache20 0 279m 7912 2108 S 0.0 1.6 0:05.63 271m httpd 11287 apache20 0 276m 5176 1936 S 0.0 1.0 0:08.66 271m httpd 14813 apache20 0 280m 9336 2424 S 0.0 1.9 0:03.00 271m httpd 1729 root 20 0 242m 396 252 S 0.0 0.1 1:14.37 242m rsyslogd 11304 postgres 20 0 218m 15m 15m S 0.0 3.2 1:34.18 203m postmaster 11323 postgres 20 0 218m 20m 16m S 0.0 4.2 1:58.70 198m postmaster 20149 postgres 20 0 218m 22m 17m S 0.0 4.7 0:07.35 195m postmaster 11360 postgres 20 0 218m 23m 17m S 0.0 4.8 1:14.27 194m postmaster 11604 postgres 20 0 218m 23m 17m S 0.0 4.9 1:43.92 194m postmaster 11531 postgres 20 0 218m 24m 17m S 0.0 4.9 2:29.91 194m postmaster 11628 postgres 20 0 218m 24m 17m S 0.0 5.0 2:46.56 194m postmaster 11437 postgres 20 0 218m 24m 17m S 0.0 4.9 1:38.11 194m postmaster 28295 postgres 20 0 188m 3364 2920 S 0.0 0.7 0:00.09 184m postmaster 13465 postgres 20 0 184m 140 96 S 0.0 0.0 15:07.25 183m postmaster 13466 postgres 20 0 184m 596 300 S 0.0 0.1 6:14.63 183m postmaster 13460 postgres 20 0 184m 340 220 S 0.0 0.1 5:14.39 183m postmaster 13555 postgres 20 0 218m 35m 17m S 0.0 7.3 1:28.89 183m postmaster 13464 postgres 20 0 184m 3332 3168 S 0.3 0.7 16:38.80 180m postmaster 11761 postgres 20 0 218m 37m 17m S 0.0 7.7 1:41.28 180m postmaster 11560 postgres 20 0 218m 38m 17m S 0.0 7.8 1:37.13 180m postmaster 12914 postgres 20 0 218m 39m 17m S 0.0 8.1 1:49.34 179m postmaster 11305 postgres 20 0 202m 24m 17m S 0.0 5.0 1:31.30 178m postmaster 29837 postgres 20 0 188m 10m 8332 S 0.0 2.1 0:01.42 178m postmaster 12666 postgres 20 0 218m 40m 17m S 0.0 8.4 0:59.64 177m postmaster 19639 postgres 20 0 216m 41m 17m S 0.0 8.5 1:38.68 175m postmaster 11373 postgres 20 0 218m 44m 18m S 0.0 9.2 1:39.24 173m postmaster 12196 postgres 20 0 195m 23m 16m S 0.0 4.8 1:15.28 172m postmaster 2313 postgres 20 0 202m 31m 18m S 0.0 6.4 1:09.09 171m postmaster 14947 postgres 20 0 218m 47m 18m S 0.0 9.7 0:30.29 170m postmaster [/code] This top result is ordered by swap. This is enough information for some clues on how to increase the response time in queries? Best Regards, -- 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] Queries take long long(10 sec.) time running
On 7 Prosinec 2011, 10:27, Andre Lopes wrote: Hi, I have a vertical database schema running with an webapp. I query this database in the webapp rotating the vertical schema with a view(something like a pivot view) I know that this type of operation is very expensive, but I have the webapp running with acceptable response time for 45 days(machine uptime). Now the queries started to take much much time to execute. I use a VPS with 512MB memory, and I have Apache2, PostgreSQL 8.4, MySQL 5. The webapp don't have high traffic, have about 700 visits per day... I've run top and I see that the server is using a lot of swap. I have ordered the top to give me the used swap, the result is this: The swap is probably the reason why it's so slow. Anyway top output is rather useless in this case, especially when ordered by SWAP. We have no clue which of the processes is the one running the query Post vmstat output, e.g. 10 lines of vmstat 1 when the query is running. Get rid of the swap and read this: http://wiki.postgresql.org/wiki/Slow_Query_Questions You should post at least EXPLAIN ANALYZE of the query and info about the settings of the database (shared buffers etc.). Tomas -- 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] Queries take long long(10 sec.) time running
On 12/07/11 1:27 AM, Andre Lopes wrote: This is enough information for some clues on how to increase the response time in queries? no. to optimize queries, you generally need to know what the queries are, what the relations they are using look like, and get the output of `explain analyze your query;` some random comments in passing... * whats mysql got to do with this? * a server running efficiently should be using zero swap. the fact that you have 340MB of swap used implies you need at least twice as much physical memory as you have. * VPS? as in virtual server? so your disk IO is virtualized too? this is usually bad news for getting decent database performance. -- john r pierceN 37, W 122 santa cruz ca mid-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
Re: [GENERAL] [pgadmin-support] Help for Migration
On 12/07/2011 05:44 PM, mamatha_kagathi_c...@dell.com wrote: I am using postgres 9.0.4 I don't think you are, actually, I think you're using EnterpriseDB Postgres Plus Advanced Server. Please past the output of the SELECT version(); command. Here's what happens if you try CREATE PROCEDURE on PostgreSQL: $ psql regress psql (9.1.1) Type help for help. regress= select version(); version - PostgreSQL 9.1.1 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.6.1 20110824 (Red Hat 4.6.1-8), 64-bit (1 row) regress= CREATE OR REPLACE PROCEDURE test () RETURNS void AS $$ regress$ BEGIN regress$ RETURN; regress$ END; regress$ $$ LANGUAGE 'plpgsql'; ERROR: syntax error at or near PROCEDURE LINE 1: CREATE OR REPLACE PROCEDURE test () RETURNS void AS $$ ^ I can actually use EXEC in psql which seem to be throwing some error in Craig's case. That's because it seems you're not actually using PostgreSQL. You need to contact EnterpriseDB technical support for assistance if you are using EnterpriseDB. If it's another product, please specify. -- Craig Ringer -- 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] Queries take long long(10 sec.) time running
Thanks for the replies. I've done a vmstat 1, here is the result: r b swpd free buff cache si sobibo in cs us sy id wa st 1 1 342728 21384264 34940 960 1428 0 616 275 1 0 92 7 0 0 0 342728 15032312 4000800 5136 0 821 561 0 0 89 10 0 0 0 342728 15048312 4007200 0 0 86 51 0 0 100 0 0 0 0 342728 15792336 4027200 256 0 561 201 0 0 94 5 0 0 0 342728 15808344 4030000 036 92 60 0 0 100 0 0 0 0 342728 15800344 4030400 0 0 366 171 0 0 100 0 0 0 0 342728 15800344 4030400 0 0 67 44 0 0 100 0 0 0 0 342728 15800344 4030400 0 0 351 163 0 0 100 0 0 0 0 342728 15800344 4030400 0 0 85 52 0 0 100 0 0 0 1 342728 14808344 4087600 52840 459 200 0 0 96 4 0 0 1 342728 14596356 41408 320 63688 217 80 0 0 95 5 0 1 1 340184 10876356 41428 41440 4144 0 1155 715 0 0 82 17 0 0 3 333176 5460316 38484 10312 28 1033632 2158 1474 3 1 75 21 0 0 2 329480 6192148 31452 7688 1764 8000 1812 2140 1311 2 1 74 23 0 1 1 329112 6452136 30836 4284 2268 5828 2296 1638 799 6 0 75 19 0 0 3 328832 5972120 30356 3572 2312 5396 2316 1388 744 4 0 72 24 0 1 5 331156 5204116 30656 764 2988 2136 3036 552 391 0 0 68 32 0 1 0 329708 6524104 30100 2176 188 3256 192 1586 517 8 0 77 14 0 0 1 330748 6580104 30840 1244 1764 5112 1764 1270 509 5 0 70 25 0 1 1 330584 5888124 31204 1160 1180 2936 1184 1110 381 7 0 76 17 0 1 1 332684 5660200 34564 936 2928 14252 3452 2812 1191 9 1 61 28 0 1 2 332500 5916220 34216 860 580 2084 1160 1151 542 4 0 66 30 0 0 2 332236 5596228 33712 1636 1088 2444 1132 1665 475 10 1 64 25 0 1 1 332552 6232152 32580 1180 1192 4948 1192 1386 504 6 0 61 32 0 1 0 332748 6496192 34380 32 204 2128 204 1804 363 13 0 78 9 0 0 4 334712 6056244 34644 356 2072 4836 2168 1752 542 8 1 71 21 0 0 2 335060 5792260 35132 100 372 1284 372 506 212 0 0 76 24 0 1 0 335492 6572280 36352 96 480 2516 576 769 325 1 0 76 23 0 0 0 335492 6568280 3643200 0 0 264 120 0 0 100 0 0 Seems my problem is SWAP/IO... I can only solve this putting more RAM on the machine or it is possible to put down this values only adjusting some settings? Best Regards, On Wed, Dec 7, 2011 at 9:52 AM, John R Pierce pie...@hogranch.com wrote: On 12/07/11 1:27 AM, Andre Lopes wrote: This is enough information for some clues on how to increase the response time in queries? no. to optimize queries, you generally need to know what the queries are, what the relations they are using look like, and get the output of `explain analyze your query;` some random comments in passing... * whats mysql got to do with this? * a server running efficiently should be using zero swap. the fact that you have 340MB of swap used implies you need at least twice as much physical memory as you have. * VPS? as in virtual server? so your disk IO is virtualized too? this is usually bad news for getting decent database performance. -- john r pierce N 37, W 122 santa cruz ca mid-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 -- 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] Queries take long long(10 sec.) time running
On 7 Prosinec 2011, 11:28, Andre Lopes wrote: Thanks for the replies. Seems my problem is SWAP/IO... I can only solve this putting more RAM on the machine or it is possible to put down this values only adjusting some settings? You can set PostgreSQL, MySQL etc. to use less memory - so that it fits into the RAM. What are the basic config parameters, i.e. shared_buffers, work_mem etc.? The question is why it worked fine for 45 days and then it started to slow down. My bet is that the database grew for some reason (users entering data, bloat ...). This often triggers sudden performance degradation. Tomas -- 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] [pgadmin-support] Help for Migration
On 12/07/2011 06:57 PM, mamatha_kagathi_c...@dell.com wrote: HI Craig, Yes I am using EnterpriseDB Postgres Plus Advanced Server. But does that mean Postgres 9.0 version from Postgres community and Postgres9.0 version from EnterpriseDB works differently? Yes! They're different things. EnterpriseDB adds an Oracle compatibility layer, stored procedures, and all sorts of other little extras. If they were the same, why would people pay for EnterpriseDB Advanced Server? They might pay for support, but not an up-front license fee for a product where they could download it for free... You still haven't posted select version(). That is one of the first items in this page: http://wiki.postgresql.org/wiki/Guide_to_reporting_problems ... which I strongly suggest that you read, because following it would've saved all of us a lot of hassle and confusion. And Postgres9.0 from community has a limitation for procedures? Yes! PostgreSQL (as of version 9.1 at least) has NO support for stored procedures. It supports user-defined stored functions in a variety of languages, but no stand-alone procedures. It emulates stored procedures by invoking a stored function stand-alone as, eg: SELECT somefuncname(); but those functions can't do things like BEGIN/COMMIT, etc. I can also get the same result if I execute it in pgadmin (version downloaded from postgres community) which is on a different client machine but connected to the server on enterpriseDB version As EXEC proc. If you're connected to EnterpriseDB, I'd expect that. If you're connected to PostgreSQL, maybe PgAdmin is translating EXEC into a SELECT ? -- Craig Ringer
[GENERAL] is there example of update skript?
Hello
I am playing with extensions implemented in plpgsql. I am checking update.
I have a simple extension
file gdlib--1.1.sql
CREATE OR REPLACE FUNCTION gdlib_version()
RETURNS numeric AS $$
SELECT 1.1;
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION hello(text)
RETURNS text AS $$
SELECT 'Hello, ' || $1 || ' from gdlib ' || gdlib_version();
$$ LANGUAGE sql;
file gdlib--1.0.sql
CREATE OR REPLACE FUNCTION gdlib_version()
RETURNS numeric AS $$
SELECT 1.0;
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION hello(text)
RETURNS text AS $$
SELECT 'Hello, ' || $1 || ' from gdlib ' || gdlib_version();
$$ LANGUAGE sql;
I created a empty update files (it's probably wrong)
postgres=# SELECT * FROM pg_extension_update_paths('gdlib');
source | target | path
++--
1.0| 1.1| 1.0--1.1
1.1| 1.0| 1.1--1.0
(2 rows)
Issue
After ALTER EXTENSION gdlib UPDATE TO '1.1'
I have 1.0 function still?
I expected a refresh 1.1 sql script, but it was newer loaded
What are correct steps?
Regards
Pavel Stehule
--
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] [pgadmin-support] Help for Migration
On Wed, 2011-12-07 at 21:23 +0800, Craig Ringer wrote: On 12/07/2011 06:57 PM, mamatha_kagathi_c...@dell.com wrote: [...] I can also get the same result if I execute it in pgadmin (version downloaded from postgres community) which is on a different client machine but connected to the server on enterpriseDB version As EXEC proc. If you're connected to EnterpriseDB, I'd expect that. If you're connected to PostgreSQL, maybe PgAdmin is translating EXEC into a SELECT ? pgAdmin doesn't translate queries executed by the user. If EXEC works on EDB AS, then pgadmin will fire it with success. If it doesn't, pgadmin will fire it, and the result will be a failure. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.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] [pgadmin-support] Help for Migration
On Wed, 2011-12-07 at 14:30 +0100, Guillaume Lelarge wrote: On Wed, 2011-12-07 at 21:23 +0800, Craig Ringer wrote: On 12/07/2011 06:57 PM, mamatha_kagathi_c...@dell.com wrote: [...] I can also get the same result if I execute it in pgadmin (version downloaded from postgres community) which is on a different client machine but connected to the server on enterpriseDB version As EXEC proc. If you're connected to EnterpriseDB, I'd expect that. If you're connected to PostgreSQL, maybe PgAdmin is translating EXEC into a SELECT ? pgAdmin doesn't translate queries executed by the user. If EXEC works on EDB AS, then pgadmin will fire it with success. If it doesn't, pgadmin will fire it, and the result will be a failure. BTW, pgAdmin allows to open the query tool with an EXEC script when the user has selected a procedure (EDB AS object). So I guess EDB AS supports the EXEC statement :) -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] postgres 9.0.4 Streaming relate question ..
Hi All - Need some help. We have been using postgres 9.0.4 for our production environment for some time. Today we are going to restart the Solaris servers. We have streaming on . My question is how to handle the streaming when we start the database on the servers. Can you please give me some steps. I have actually used the http://wiki.postgresql.org/wiki/Streaming_Replication to set up the replication. Appreciate your help Regards
[GENERAL] Convert / Migrate From Oracle 11gR2 To PostgreSQL ? On CentOS 5.7 x86_64
This has to do with my personal home environment. I'm running Oracle 11gR2 DBMS with 4 instances using a single home directory. For each of the instances I'm using LVM file systems with 10 logical volumes defined (/dbmsu00 used for the install and /dbmsu01 - u09 for the required files created using the DBCA utility). A single LISTENER created using the NETCA utility. I chose Oracle because back in the dark ages I was a Oracle DBA when Oracle 7.3 was current. I've been told that it's OK to download and use the Oracle DBMS in a noncommercial environment for a limited time - well it's reached the end of that time. I can't use the free Oracle XE because the database size limitations (4 GB). Based upon what I've read and seen, the best choice for me is PostgreSQL Community Edition. What I need is advice, documentation, and information on how to take the above and move it from Oracle to PostgreSQL along with updating my JBoss 6.0 environment so it has access to the PostgreSQL databases/instances? Can someone supply me with some direction? Thanks, Gene Poole + It's impossible for everything to be true. +
Re: [GENERAL] Convert / Migrate From Oracle 11gR2 To PostgreSQL ? On CentOS 5.7 x86_64
On 7 Prosinec 2011, 15:13, Gene Poole wrote: This has to do with my personal home environment. I'm running Oracle 11gR2 DBMS with 4 instances using a single home directory. For each of the instances I'm using LVM file systems with 10 logical volumes defined (/dbmsu00 used for the install and /dbmsu01 - u09 for the required files created using the DBCA utility). A single LISTENER created using the NETCA utility. Not sure why you're doing this, but you can do something similar with PostgreSQL. Running four clusters using the same set of binaries is not a big deal. I chose Oracle because back in the dark ages I was a Oracle DBA when Oracle 7.3 was current. I've been told that it's OK to download and use the Oracle DBMS in a noncommercial environment for a limited time - well it's reached the end of that time. I can't use the free Oracle XE because the database size limitations (4 GB). Although this is a PostgreSQL list, I'll point out the limit is 11GB with the current Oracle XE version (11g). Based upon what I've read and seen, the best choice for me is PostgreSQL Community Edition. What I need is advice, documentation, and information on how to take the above and move it from Oracle to PostgreSQL along with updating my JBoss 6.0 environment so it has access to the PostgreSQL databases/instances? Can someone supply me with some direction? PostgreSQL Community Edition? What is that? Do you mean sources that are available for download from postgresql.org? Switching the database in the AS is not a big deal - there's usually a connection pool, so you need to add PostgreSQL JDBC driver and change the config. Porting the application is usually much harder, especially if you use features that are available on Oracle only (or if you're hurt by some Oracle-ism). If you're using native SQL, PL/SQL, etc. you'll have to rewrite that. The complexity really depends on how your application is coded, it's impossible to judge this. Anyway, the simplest thing you can do is to give it a try. Install PostgreSQL on a development environment and try to run the application. It'll probably fail for some reason - fix the issue and repeat. There are tools to make the switch easier - e.g. orafce package that provides some compatibility features. Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] making \pset pager off the default
Hi: How can I make pager off the default when running psql ? v8.3.4 and v9.0.1 on linux.
Re: [GENERAL] making \pset pager off the default
On Wednesday, December 07, 2011 6:45:12 am Gauthier, Dave wrote: Hi: How can I make pager off the default when running psql ? v8.3.4 and v9.0.1 on linux. Use a psqlrc file and add \pset pager off: http://www.postgresql.org/docs/9.0/interactive/app-psql.html Files Unless it is passed an -X or -c option, psql attempts to read and execute commands from the system-wide psqlrc file and the user's ~/.psqlrc file before starting up. (On Windows, the user's startup file is named %APPDATA%\postgresql\psqlrc.conf.) See PREFIX/share/psqlrc.sample for information on setting up the system-wide file. It could be used to set up the client or the server to taste (using the \set and SET commands). Both the system-wide psqlrc file and the user's ~/.psqlrc file can be made version-specific by appending a dash and the PostgreSQL release number, for example ~/.psqlrc-9.0.6. A matching version-specific file will be read in preference to a non-version-specific file. The command-line history is stored in the file ~/.psql_history, or %APPDATA%\postgresql\psql_history on Windows. ' -- 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
[GENERAL] Expanding a VARCHAR on a large table that has inherited subtables ...
Hello, I have a table that has several inherited sub-tables with a partition constraint restriction. All the sub-tables have the same structure and they are used to distribute load, each sub-table has typically more than 6million records. When I try to issue a ALTER TABLEtablename ALTER COLUMNcolumnname TYPE VARCHAR(16); I receive a ERROR: constraint must be added to child tables too The main table, which has no data in it, has a constraint: constraintname CHECK (columnname IS NULL OR substring(columnname::text, '^[a-zA-Z0-9_,. -]*$'::text) IS NOT NULL AND substring( columnname::text, '^[a-zA-Z0-9_,. -]*$'::text) =columnname::text) This same constraint is on the subtables since they are created with: CREATE TABLEsubtablename (LIKEtablename INCLUDING ALL); ALTER TABLEsubtablename INHERITtablename; My question is: 1. Should I create a PL/PGSQL script to: 1. go to each sub-table 1. drop constraint from sub-table 2. remove inheritance of sub-table 3. alter column on sub-table 4. add constraint again to sub-table 2. drop constraint from main table 3. alter column on main table 4. add constraint to main table 5. go to each of previous sub-tables 1. set inheritance as before of sub-table to main table 2. drop the constraint on table and subtables, apply the alter table alter column and add the constraint again 3. any other way? Best regards, Paulo Correia
Re: [GENERAL] making \pset pager off the default
Like a charm. Thanks! -Original Message- From: Adrian Klaver [mailto:adrian.kla...@gmail.com] Sent: Wednesday, December 07, 2011 9:57 AM To: pgsql-general@postgresql.org Cc: Gauthier, Dave Subject: Re: [GENERAL] making \pset pager off the default On Wednesday, December 07, 2011 6:45:12 am Gauthier, Dave wrote: Hi: How can I make pager off the default when running psql ? v8.3.4 and v9.0.1 on linux. Use a psqlrc file and add \pset pager off: http://www.postgresql.org/docs/9.0/interactive/app-psql.html Files Unless it is passed an -X or -c option, psql attempts to read and execute commands from the system-wide psqlrc file and the user's ~/.psqlrc file before starting up. (On Windows, the user's startup file is named %APPDATA%\postgresql\psqlrc.conf.) See PREFIX/share/psqlrc.sample for information on setting up the system-wide file. It could be used to set up the client or the server to taste (using the \set and SET commands). Both the system-wide psqlrc file and the user's ~/.psqlrc file can be made version-specific by appending a dash and the PostgreSQL release number, for example ~/.psqlrc-9.0.6. A matching version-specific file will be read in preference to a non-version-specific file. The command-line history is stored in the file ~/.psql_history, or %APPDATA%\postgresql\psql_history on Windows. ' -- 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] is there example of update skript?
Le 7 décembre 2011 14:26, Pavel Stehule pavel.steh...@gmail.com a écrit :
Hello
I am playing with extensions implemented in plpgsql. I am checking update.
I have a simple extension
file gdlib--1.1.sql
CREATE OR REPLACE FUNCTION gdlib_version()
RETURNS numeric AS $$
SELECT 1.1;
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION hello(text)
RETURNS text AS $$
SELECT 'Hello, ' || $1 || ' from gdlib ' || gdlib_version();
$$ LANGUAGE sql;
file gdlib--1.0.sql
CREATE OR REPLACE FUNCTION gdlib_version()
RETURNS numeric AS $$
SELECT 1.0;
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION hello(text)
RETURNS text AS $$
SELECT 'Hello, ' || $1 || ' from gdlib ' || gdlib_version();
$$ LANGUAGE sql;
I created a empty update files (it's probably wrong)
postgres=# SELECT * FROM pg_extension_update_paths('gdlib');
source | target | path
++--
1.0 | 1.1 | 1.0--1.1
1.1 | 1.0 | 1.1--1.0
(2 rows)
Issue
After ALTER EXTENSION gdlib UPDATE TO '1.1'
I have 1.0 function still?
in the path, so you can run 1.1-1.0 if you provided a script for that.
I expected a refresh 1.1 sql script, but it was newer loaded
What are correct steps?
upgrade the schema/function from 1.0 to 1.1 in your
extension--last--new.sql file.
A blank file can be provided to just update the version number of the extension.
.so are *not* versionned so once you've install the new 1.1, the old
sql will access to it.
If you change sql API for your C extension, you need to run ALTER EXT
UPDATE, else it is not required. (expect to increase version number)
Regards
Pavel Stehule
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation
--
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] making \pset pager off the default
On Wed, Dec 7, 2011 at 7:45 AM, Gauthier, Dave dave.gauth...@intel.com wrote: Hi: How can I make pager off the default when running psql ? v8.3.4 and v9.0.1 on linux. I'm sure there are more elegant ways, but: postgres@ironton:~ export PAGER=/bin/cat postgres@ironton:~ psql postgres=# select * from a; (output runs right off the bottom of the page...) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] dblink performance
Hello All, I'm currently on a development team utilizing PostgreSQL and we are looking into the possibility of using dblink to reference an external database (read only). Our system administrator and DBA were concerned about the performance impact that cross-database queries would have on a production application. Are there any known performance issues or anything of the like that I would need to know before pushing this issue further? I have been using PostgreSQL for the past couple months but this is my first time using dblink. I really just need an opinion from someone who has used this technology before. Thanks! Alexander E. Bible 700 Delaware Ave. Buffalo, NY 14226 Tel: (716)-888-3687 Mobile: (716)-472-9655 alex.bi...@ctg.com The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you are not the intended recipient of this message, please contact the sender and delete this material from this computer. image001.gif
Re: [GENERAL] [pgadmin-support] Help for Migration
Hi Craig,
The procedure definition is
CREATE OR REPLACE PROCEDURE employeedetailinsert(dellbadgeid character varying
DEFAULT NULL::character varying, empid character varying DEFAULT
NULL::character varying, firstname character varying DEFAULT NULL::character
varying, lastname character varying DEFAULT NULL::character varying, dob_day
character varying DEFAULT NULL::character varying, dob_month character varying
DEFAULT NULL::character varying, contactno character varying DEFAULT
NULL::character varying, email character varying DEFAULT NULL::character
varying, hiredate character varying DEFAULT NULL::character varying,
rehiredate character varying DEFAULT NULL::character varying, totalexp
character varying DEFAULT NULL::character varying, statuscd character varying
DEFAULT NULL::character varying, costcenter character varying DEFAULT
NULL::character varying, location character varying DEFAULT NULL::character
varying, grade character varying DEFAULT NULL::character varying, hrmgrid
character varying DEFAULT NULL::character varying, hrorgmgr character varying
DEFAULT NULL::character varying, hrdepartment character varying DEFAULT
NULL::character varying, financedepartment character varying DEFAULT
NULL::character varying, financedepartmentdescription character varying
DEFAULT NULL::character varying, passport character varying DEFAULT
NULL::character varying, passvalidfrom character varying DEFAULT
NULL::character varying, passvalidupto character varying DEFAULT
NULL::character varying) AS...
-- So I am not calling a function but a procedure.
The ODBC string is:
conn.Open Provider=PGNP.1;Password=PGDBTest5;Persist Security Info=True;User
ID=enterprisedb;Initial Catalog=edb;Data
Source=ip_address;PORT=5444;SSL=allow;
here IP address is replaced for discretion.
I have all reasons to believe that this connection string works as I am able to
connect to other pages where the database-ASP page interaction is directly
using select, insert and update statements rather than calling procedures.
Finally I have copy-pasted the exec proc form the error in Pgadmin and
executed the same to get desirable results. So I doubt if the parameters have
anything to do with this.
Hope I have provided all the information. Please let me know if I am still lost
somewhere.
Thanks,
Mamatha
-Original Message-
From: Craig Ringer [mailto:ring...@ringerc.id.au]
Sent: Wednesday, December 07, 2011 1:48 PM
To: Chan, Mamatha Kagathi
Cc: haram...@gmail.com; pgsql-general@postgresql.org
Subject: Re: [GENERAL] [pgadmin-support] Help for Migration
On 12/07/2011 03:23 PM, mamatha_kagathi_c...@dell.com wrote:
Hi Alban/Craig,
Employeedetailinsert is procedure I have created in PostgreSQL. When I
try to execute the procedure directly in Pgadmin as
EXEC Employeedetailinsert (parameters same as below) It works fine with
desired result.
PgAdmin uses libpq directly. It must be translating the `EXEC' into something
PostgreSQL can understand, because the psql command line tool (which also uses
libpq) doesn't know what EXEC means.
regress= EXEC dummyfunction();
ERROR: syntax error at or near EXEC
LINE 1: EXEC dummyfunction();
But When I call the procedure in Classic ASP I get the below error. I do not
understand why the driver assumes it as function in the 1st place. I am using
Postgres Native driver .
Since you're talking about ODBC, I presume you're *actually* using PsqlODBC as
your database driver. AFAIK there's no such thing as postgres native driver.
In the call when I am using CALL as suggested below
OK, and since you're using ODBC the CALL gets translated to a server-side proc
invocation, that should be no problem.
You're not actually showing your ODBC code or ODBC query string, which would be
helpful.
Executing Procedure =EXEC employeedetailinsert( '
123','55','Mamatha','Chandrashekar','06','05','9886269427','mamatha...@dell.com','12/10/2010','','7','Active','','Bangalore','IG','906','Muralikrishna','TG-,'TPDBA01','TPDBA01-
DBA Practice','No','','') PostgreSQL Native Provider error '80040e14'
ERROR: function employeedetailinsert(unknown, unknown, unknown, unknown,
unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown,
unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown,
unknown, unknown, unknown) does not exist LINE 1: SELECT * FROM
employeedetailinsert(' 123','55','Mamat... ^ HINT: No function matches the
given name and argument types. You might need to add explicit type casts.
Possibilities:
- You might have defined your function with a double-quoted name, eg
Employeedetailinsert. This makes it case sensitive. If that's the case,
you'll see it with a capital letter when you run the \df command in psql or
browse functions in PgAdmin;
- You've mucked up your argument list and it isn't really the same length as
what you used in PgAdmin after all or you've got the wrong data types;
- You might need
Re: [GENERAL] [pgadmin-support] Help for Migration
I am using postgres 9.0.4 . I can actually use EXEC in psql which seem to be throwing some error in Craig's case. Also I am able to create procedure and execute them in pgadmin and psql. This piece of code was migrated from Oracle database (MS-SQL to Oracle then Oracle to Postgres). Nevertheless I am able to create independently a procedure also. Only In the ASP environmnet I am facing issue. Below is the piece of code which I executed in Psql. edb=# EXEC employeedetailinsert( '529','55','Mamatha','Chandrashekar','0 9','03','9677756894','mamatha...@dell.com','12/10/2010','','7','Active','','Bang alore','IG','206','Muralikrishna','TG-DBA','TPDB','TPDB','No','',''); EDB-SPL Procedure successfully completed -Original Message- From: Alban Hertroys [mailto:haram...@gmail.com] Sent: Wednesday, December 07, 2011 2:56 PM To: Chan, Mamatha Kagathi Cc: ring...@ringerc.id.au; pgsql-general@postgresql.org Subject: Re: [GENERAL] [pgadmin-support] Help for Migration On 7 December 2011 10:08, mamatha_kagathi_c...@dell.com wrote: The procedure definition is CREATE OR REPLACE PROCEDURE -- So I am not calling a function but a procedure. I don't think CREATE PROCEDURE is actually a valid command in Postgres. The 9.0 documentation seems to confirm that (http://www.postgresql.org/docs/9.0/static/sql-commands.html). In Postgres, procedures are void-returning functions - there's no difference. It's just a naming convention. Perhaps you're using some 3rd party code for MS-SQL compatibility? I imagine CREATE PROCEDURE would then be a simple wrapper around CREATE FUNCTION ... RETURNING void. Heh, didn't know you could define DEFAULT argument values like that, but it seems you can! -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest. -- 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] [pgadmin-support] Help for Migration
HI Craig, Yes I am using EnterpriseDB Postgres Plus Advanced Server. But does that mean Postgres 9.0 version from Postgres community and Postgres9.0 version from EnterpriseDB works differently? And Postgres9.0 from community has a limitation for procedures? I actually changed the stored procedure to Function and now I can get the desired result from psql, pgadmin and ASP consistently. I can also get the same result if I execute it in pgadmin (version downloaded from postgres community) which is on a different client machine but connected to the server on enterpriseDB version As EXEC proc. Please let me know your thoughts so that I can understand the difference between the versions. Thanks, Mamatha -Original Message- From: Craig Ringer [mailto:ring...@ringerc.id.au] Sent: Wednesday, December 07, 2011 3:37 PM To: Chan, Mamatha Kagathi Cc: haram...@gmail.com; pgsql-general@postgresql.org Subject: Re: [GENERAL] [pgadmin-support] Help for Migration On 12/07/2011 05:44 PM, mamatha_kagathi_c...@dell.com wrote: I am using postgres 9.0.4 I don't think you are, actually, I think you're using EnterpriseDB Postgres Plus Advanced Server. Please past the output of the SELECT version(); command. Here's what happens if you try CREATE PROCEDURE on PostgreSQL: $ psql regress psql (9.1.1) Type help for help. regress= select version(); version - PostgreSQL 9.1.1 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.6.1 20110824 (Red Hat 4.6.1-8), 64-bit (1 row) regress= CREATE OR REPLACE PROCEDURE test () RETURNS void AS $$ regress$ BEGIN regress$ RETURN; regress$ END; regress$ $$ LANGUAGE 'plpgsql'; ERROR: syntax error at or near PROCEDURE LINE 1: CREATE OR REPLACE PROCEDURE test () RETURNS void AS $$ ^ I can actually use EXEC in psql which seem to be throwing some error in Craig's case. That's because it seems you're not actually using PostgreSQL. You need to contact EnterpriseDB technical support for assistance if you are using EnterpriseDB. If it's another product, please specify. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Slow COMMIT statements
Hello List, 2011-12-07 11:52:52 CET 8075 xxx [local]LOG: duration: 1168.953 ms statement: COMMIT 2011-12-07 11:52:52 CET 7327 xxx [local]LOG: duration: 1116.119 ms statement: COMMIT 2011-12-07 11:52:52 CET 31727 xxx [local]LOG: duration: 1131.044 ms statement: COMMIT 2011-12-07 11:52:52 CET 25077 xxx [local]LOG: duration: 1064.599 ms statement: COMMIT these messages show up in the postgresql logs at irregular intervals. We got no slow queries before or after these statements. Checkpoint logging is enabled also but these don't show up either, well they do but minutes later ;). I would like to debug these slow COMMITs but don't know what is causing them. The server itself is idling most of the time... with a load avg around 1-2(with 64 cores) and iowait around 0.5 - 1%. Notable changes from default config: log_min_duration_statement = 500 log_checkpoints = on checkpoint_segments = 40 Regards, Felix
[GENERAL] Fwd: postgres 9.0.4 Streaming related question ..
Small Clarification. I have gone through the documentation. I did not find any thing related to start ans stop replication after the replication is setup 1. If I shutdown the primary 2. Shutdown the slave 3. start Primary 4. start slave Will this automatically catch up the streaming. Can you please comment? Regards -- Forwarded message -- From: akp geek akpg...@gmail.com Date: Wed, Dec 7, 2011 at 9:03 AM Subject: postgres 9.0.4 Streaming relate question .. To: pgsql-general pgsql-general@postgresql.org Hi All - Need some help. We have been using postgres 9.0.4 for our production environment for some time. Today we are going to restart the Solaris servers. We have streaming on . My question is how to handle the streaming when we start the database on the servers. Can you please give me some steps. I have actually used the http://wiki.postgresql.org/wiki/Streaming_Replication to set up the replication. Appreciate your help Regards
Re: [GENERAL] Fwd: postgres 9.0.4 Streaming related question ..
On Dec 7, 2011, at 8:45 AM, akp geek wrote: Small Clarification. I have gone through the documentation. I did not find any thing related to start ans stop replication after the replication is setup 1. If I shutdown the primary 2. Shutdown the slave 3. start Primary 4. start slave Will this automatically catch up the streaming. Can you please comment? So long as you leave your recovery.conf file in place, and don't loose any needed wal files between steps 3 and 4 (or can recover them from an archive), then the answer is yes. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Tables creation date and time
Hello All Could you please help me to find out of tables creation date and time in database ? Thanks P Bharucha
Re: [GENERAL] dblink performance
On Wed, Dec 7, 2011 at 10:14 AM, Alex Bible alex.bi...@ctg.com wrote: Hello All, I’m currently on a development team utilizing PostgreSQL and we are looking into the possibility of using dblink to reference an external database (read only). Our system administrator and DBA were concerned about the performance impact that cross-database queries would have on a production application. Are there any known performance issues or anything of the like that I would need to know before pushing this issue further? I have been using PostgreSQL for the past couple months but this is my first time using dblink. I really just need an opinion from someone who has used this technology before. Thanks! dblink is a very thin wrapper for libpq. From the querying database, the overhead is pretty light -- basically the query is fired and the results are interpreted from text into whatever the database has in the receiving result via the various typein functions. For all intents and purposes, this is pretty similar to sending in queries over the regular sql interface. One gotcha of course is that libpq buffers the entire result in memory which can be dangerous, so be advised. To the receiving database, dblink queries are no different from any other query, except that they are not parameterized. Lack of parameterization and access to the binary protocol are the major downsides when using dblink. IMNSHO, dblink needs a variable argument call that uses the paramterized interface. Also support for binary transfer of data would be nice. 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] Tables creation date and time
On Wed, Dec 7, 2011 at 12:09 PM, Prashant Bharucha prashantbharu...@yahoo.ca wrote: Hello All Could you please help me to find out of tables creation date and time in database ? Generally speaking the easiest and simplest way is to log such information and then go through the logs to find out. Otherwise, there's no way that this information is really stored in pgsql. -- 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] dblink performance
On Wed, Dec 7, 2011 at 10:14 AM, Alex Bible alex.bi...@ctg.com wrote: Hello All, I'm currently on a development team utilizing PostgreSQL and we are looking into the possibility of using dblink to reference an external database (read only). Our system administrator and DBA were concerned about the performance impact that cross-database queries would have on a production application. Are there any known performance issues or anything of the like that I would need to know before pushing this issue further? I have been using PostgreSQL for the past couple months but this is my first time using dblink. I really just need an opinion from someone who has used this technology before. Thanks! dblink is a very thin wrapper for libpq. From the querying database, the overhead is pretty light -- basically the query is fired and the results are interpreted from text into whatever the database has in the receiving result via the various typein functions. For all intents and purposes, this is pretty similar to sending in queries over the regular sql interface. One gotcha of course is that libpq buffers the entire result in memory which can be dangerous, so be advised. To the receiving database, dblink queries are no different from any other query, except that they are not parameterized. Lack of parameterization and access to the binary protocol are the major downsides when using dblink. IMNSHO, dblink needs a variable argument call that uses the paramterized interface. Also support for binary transfer of data would be nice. merlin I find dblink being a nice tool as long as the data volume to transfer remains low. I've evaluated it to implement a clustered Postgres environment, but gave it up due to the poor performances. Still waiting for the binary transfer before the next try ;-) reagrds, Marc Mamin -- 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] dblink performance
Le 2011-12-07 11:14, Alex Bible a écrit : Hello All, I'm currently on a development team utilizing PostgreSQL and we are looking into the possibility of using dblink to reference an external database (read only). Our system administrator and DBA were concerned about the performance impact that cross-database queries would have on a production application. Are there any known performance issues or anything of the like that I would need to know before pushing this issue further? I have been using PostgreSQL for the past couple months but this is my first time using dblink. I really just need an opinion from someone who has used this technology before. Thanks! Alexander E. Bible Hello, one problem is when you join local data with remote data. The optimizer falls short on this when finding good plans and executing. If you frequently need to join with non-volatile remote data, it's generally better to make a snapshot of remote tables. It all depends on volume and usage patterns of databases. Interesting link: http://www.postgresonline.com/journal/archives/44-Using-DbLink-to-access-other-PostgreSQL-Databases-and-Servers.html hope that's help Bruno Lavoie b...@brunol.com bruno.lav...@gmail.com
Re: [GENERAL] dblink performance
On Wed, Dec 7, 2011 at 2:47 PM, Marc Mamin m.ma...@intershop.de wrote: On Wed, Dec 7, 2011 at 10:14 AM, Alex Bible alex.bi...@ctg.com wrote: Hello All, I'm currently on a development team utilizing PostgreSQL and we are looking into the possibility of using dblink to reference an external database (read only). Our system administrator and DBA were concerned about the performance impact that cross-database queries would have on a production application. Are there any known performance issues or anything of the like that I would need to know before pushing this issue further? I have been using PostgreSQL for the past couple months but this is my first time using dblink. I really just need an opinion from someone who has used this technology before. Thanks! dblink is a very thin wrapper for libpq. From the querying database, the overhead is pretty light -- basically the query is fired and the results are interpreted from text into whatever the database has in the receiving result via the various typein functions. For all intents and purposes, this is pretty similar to sending in queries over the regular sql interface. One gotcha of course is that libpq buffers the entire result in memory which can be dangerous, so be advised. To the receiving database, dblink queries are no different from any other query, except that they are not parameterized. Lack of parameterization and access to the binary protocol are the major downsides when using dblink. IMNSHO, dblink needs a variable argument call that uses the paramterized interface. Also support for binary transfer of data would be nice. merlin I find dblink being a nice tool as long as the data volume to transfer remains low. I've evaluated it to implement a clustered Postgres environment, but gave it up due to the poor performances. Still waiting for the binary transfer before the next try ;-) Binary transfer is not a super big deal in terms of performance actually in the general case. It's only substantially faster in a few cases like timestamp, geo types, and of course bytea. Lack of parameterization I find to be a bigger deal actually -- it's more of a usability headache than a performance thing. Also FYI binary dblink between databases is going to be problematic for any non built in type unless the type oids are synchronized across databases. 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] dblink performance
I find dblink being a nice tool as long as the data volume to transfer remains low. I've evaluated it to implement a clustered Postgres environment, but gave it up due to the poor performances. Still waiting for the binary transfer before the next try ;-) Binary transfer is not a super big deal in terms of performance actually in the general case. It's only substantially faster in a few cases like timestamp, geo types, and of course bytea. Lack of parameterization I find to be a bigger deal actually -- it's more of a usability headache than a performance thing. Also FYI binary dblink between databases is going to be problematic for any non built in type unless the type oids are synchronized across databases. merlin Thanks, ... so I don't really understand where all the time get lost in the example I posted a few weeks ago: http://archives.postgresql.org/pgsql-general/2011-09/msg00436.php Marc Mamin -- 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] Tables creation date and time
Hello 2011/12/7 Prashant Bharucha prashantbharu...@yahoo.ca Hello All Could you please help me to find out of tables creation date and time in database ? Thanks P Bharucha If you have a PostgreSQL 9.1, you can use a security hooks and write own custom extension, that will store a timestamp to somewhere. This task is possible - I did it, but I can't to publish this work. There are no other ways - you can find a some tips on net based on ctime of datafile - but these values should be invalid. Regards Pavel
Re: [GENERAL] dblink performance
On Wed, Dec 7, 2011 at 3:37 PM, Marc Mamin m.ma...@intershop.de wrote:
I find dblink being a nice tool as long as the data volume to
transfer
remains low.
I've evaluated it to implement a clustered Postgres environment, but
gave it up due to the poor performances.
Still waiting for the binary transfer before the next try ;-)
Binary transfer is not a super big deal in terms of performance
actually in the general case. It's only substantially faster in a few
cases like timestamp, geo types, and of course bytea. Lack of
parameterization I find to be a bigger deal actually -- it's more of a
usability headache than a performance thing.
Also FYI binary dblink between databases is going to be problematic
for any non built in type unless the type oids are synchronized across
databases.
merlin
Thanks,
... so I don't really understand where all the time get lost in the
example I posted a few weeks ago:
http://archives.postgresql.org/pgsql-general/2011-09/msg00436.php
you wrote:
select count(*) from
(
select * from dblink('a','select * from test_cluster')as t1(a int)
union all
select * from dblink('a','select * from test_cluster')as t1(a int)
union all
select * from dblink('a','select * from test_cluster')as t1(a int)
)foo
is about 5 times slower than an equivalent query run locally.
working with asynchron. queries (dblink_send_query) does not bring much
benefit
so that much time seems to be spent for transfer and merge
it's not exactly fair to compare dblink to local query -- dblink
method requires having to marshal all the data over the protoocl and
un-marshal on the other end. I was seeing 3-5x times difference vs
local query but this is to be expected. note the 'union all' had
nothing to so with your performance problems. Also the querying
server can do a very special trick for count(*) since it only needs to
check tuple visibility that can't be done when doing select count(*)
from (dblink_queries).
My point up thread is that dblink is going to be comparable to other
methods that involve querying the data off the server and doing the
processing on the client side.
merlin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Query Slowdown after upgrade from 9.1.1 to 9.1.2
I upgraded to v9.1.2 a couple of days ago. Some of my queries are taking noticeably longer. Some of the slower ones would max out at about 45 seconds before. Now they are maxing out at almost 2 minutes. The only change I made to postgresql.conf was geqo_effort = 10 and this was long before this upgrade. I can provide more info if necessary. The queries involve join quite a few tables and views together and also a window query for totals. Any ideas?
[GENERAL] Hope for a new PostgreSQL era?
http://www.dbms2.com/2011/11/23/hope-for-a-new-postgresql-era/ Some of the points mentioned: - MySQL is narrowing the gap, but PostgreSQL is still ahead of MySQL in some ways. (Database extensibility if nothing else.) - Neither EnterpriseDB (which now calls itself “The enterprise PostgreSQL company”) nor the PostgreSQL community leadership have covered themselves with stewardship glory. - PostgreSQL advancement is not dead. Comments? -- 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] Hope for a new PostgreSQL era?
On Wed, Dec 7, 2011 at 7:52 PM, Rodrigo E. De León Plicet rdele...@gmail.com wrote: http://www.dbms2.com/2011/11/23/hope-for-a-new-postgresql-era/ Some of the points mentioned: - MySQL is narrowing the gap, but PostgreSQL is still ahead of MySQL in some ways. (Database extensibility if nothing else.) - Neither EnterpriseDB (which now calls itself “The enterprise PostgreSQL company”) nor the PostgreSQL community leadership have covered themselves with stewardship glory. - PostgreSQL advancement is not dead. I'd like to see the author's thoughts filled out on these points. they seem rather vague and overly simplistic, and I wonder what specific points he might have to make rather than this vague hand wavy list he has so far. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg crash shortly after 9.1.1 - 9.1.2 upgrade
All was fine until:
LOG: statement: select _devel.cleanupEvent('10 minutes'::interval,
'false'::boolean);
ERROR: could not open file base/16406/2072097_fsm: Permission denied
STATEMENT: select _devel.cleanupEvent('10 minutes'::interval,
'false'::boolean);
WARNING: AbortTransaction while in COMMIT state
PANIC: cannot abort transaction 7108311, it was already committed
LOG: unexpected EOF on client connection
LOG: unexpected EOF on client connection
LOG: unexpected EOF on client connection
LOG: server process (PID 21090) was terminated by signal 6: Aborted
LOG: terminating any other active server processes
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back
the current transaction and exit, because another server process exited
abnormally and possibly corrupted shared memory.
...
DETAIL: The postmaster has commanded this server process to roll back
the current transaction and exit, because another server process exited
abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and
repeat your command.
LOG: all server processes terminated; reinitializing
LOG: database system was interrupted; last known up at 2011-12-07
14:51:37 EST
LOG: database system was not properly shut down; automatic recovery in
progress
LOG: consistent recovery state reached at 5/C9A28BE4
LOG: redo starts at 5/C9A26DBC
FATAL: could not open file base/16406/2072097_fsm: Permission denied
CONTEXT: xlog redo commit: 2011-12-07 14:56:04.305309-05; rels:
base/16406/2072101 base/16406/2072100 base/16406/2072099
base/16406/2072098 base/16406/2072097
LOG: startup process (PID 25977) exited with exit code 1
LOG: aborting startup due to startup process failure
I was unable to restart pg until I did a chmod u+rw on that file. There
was nothing interesting in the log before the crash, just a bunch of
activity that was obviously slony.
version is:
PostgreSQL 9.1.2 on i686-pc-linux-gnu, compiled by gcc (GCC) 4.1.2
20080704 (Red Hat 4.1.2-48), 32-bit
--
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] Hope for a new PostgreSQL era?
On Wed, Dec 7, 2011 at 8:08 PM, Scott Marlowe scott.marl...@gmail.com wrote: I'd like to see the author's thoughts filled out on these points. they seem rather vague and overly simplistic, and I wonder what specific points he might have to make rather than this vague hand wavy list he has so far. Additionally I am not entirely sure what he means by the last point. If you look at the work that NTT along with EDB has put into Postgres-XC, for example, it looks to me like the Postgres ecosystem is growing by leaps and bounds and we are approaching an era where Oracle is no longer ahead in any significant use case. The thing I am personally worried about is the ability of one company to dominate the framing of PostgreSQL service offerings. For example while in the US it hasn't caught on, a lot of people at MYGOSSCON accepted EnterpriseDB's framing of the official PostgreSQL release as the community edition. If you have a single vendor which dominates the dialogue that's a bad thing. To be clear this isn't a criticism of EDB. I greatly appreciate the substantial effort they have put into building Pg awareness here in SE Asia. However, it is a caution about the recommendation that we need a corporate steward. I argue corporate stewardship would be a strong net negative because it would be first and foremost a way to crowd everyone else out. We have stewardship. It's the core committee, and it's the best kind of stewardship we can have. Here's a useful post that I was forwarded by another LSMB developer. http://openlife.cc/blogs/2010/november/how-grow-your-open-source-project-10x-and-revenues-5x Additionally, I would suggest that PostgreSQL has a lot of users because we have a great---and open---community. I think a new PostgreSQL era is coming but I don't think it will happen the way that blog poster implies. There is a tremendous need for Pg skills in SE Asia right now, and I expect this to continue to grow exponentially. PostgreSQL advancement also by my view is also not merely not dead but in fact accelerating. Best Wishes, Chris Travers -- 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] pg crash shortly after 9.1.1 - 9.1.2 upgrade
Joseph Shraibman j...@selectacast.net writes:
All was fine until:
LOG: statement: select _devel.cleanupEvent('10 minutes'::interval,
'false'::boolean);
ERROR: could not open file base/16406/2072097_fsm: Permission denied
That's pretty weird. What were the permissions on that file? Was it
properly owned by the postgres user?
WARNING: AbortTransaction while in COMMIT state
PANIC: cannot abort transaction 7108311, it was already committed
And that's even weirder, since it implies that we tried to open the FSM
file during post-commit cleanup. The commit-record printout suggests
that we probably were trying to delete the file due to a committed DROP
TABLE operation, but I don't understand why we tried to open the file
rather than just unlink it.
Can you reproduce this? Can you show us what cleanupEvent() does?
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
[GENERAL] Very slow table
Hi, I am using squeryl, which is based on jdbc, and I am trying to run the following statement: delete from Entry where (id = 0) but the query never seems to finish. I have tried it in both the program I am writing, and the above statement in pgadmin3, and both cases never finish. Other queries, like delete from Entry where true complete. The table's initialization statement is: CREATE TABLE Entry ( pID bigint NOT NULL, id bigint NOT NULL, r text NOT NULL, CONSTRAINT Entry_pkey PRIMARY KEY (id), CONSTRAINT EntryFK21 FOREIGN KEY (pID) REFERENCES PP (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ) WITH ( OIDS=FALSE ); ALTER TABLE Entry OWNER TO riri; CREATE INDEX idx2eda05d9 ON Entry USING btree (id); Other information: Postgres Version: PostgreSQL 8.4.9 on i686-pc-linux-gnu, compiled by GCC gcc-4.5.real (Ubuntu/Linaro 4.5.2-8ubuntu4) 4.5.2, 32-bit Thanks, Richard Weiss.
Re: [GENERAL] [pgadmin-support] Help for Migration
Thanks everybody for helping me understand how postgres works with stored programs. I will remember in future to put certain information clearly as mentioned in the website suggested by Craig. Thanks for that as well. From: Craig Ringer [mailto:ring...@ringerc.id.au] Sent: Wednesday, December 07, 2011 6:53 PM To: Chan, Mamatha Kagathi Cc: haram...@gmail.com; pgsql-general@postgresql.org Subject: Re: [GENERAL] [pgadmin-support] Help for Migration On 12/07/2011 06:57 PM, mamatha_kagathi_c...@dell.commailto:mamatha_kagathi_c...@dell.com wrote: HI Craig, Yes I am using EnterpriseDB Postgres Plus Advanced Server. But does that mean Postgres 9.0 version from Postgres community and Postgres9.0 version from EnterpriseDB works differently? Yes! They're different things. EnterpriseDB adds an Oracle compatibility layer, stored procedures, and all sorts of other little extras. If they were the same, why would people pay for EnterpriseDB Advanced Server? They might pay for support, but not an up-front license fee for a product where they could download it for free... You still haven't posted select version(). That is one of the first items in this page: http://wiki.postgresql.org/wiki/Guide_to_reporting_problems ... which I strongly suggest that you read, because following it would've saved all of us a lot of hassle and confusion. And Postgres9.0 from community has a limitation for procedures? Yes! PostgreSQL (as of version 9.1 at least) has NO support for stored procedures. It supports user-defined stored functions in a variety of languages, but no stand-alone procedures. It emulates stored procedures by invoking a stored function stand-alone as, eg: SELECT somefuncname(); but those functions can't do things like BEGIN/COMMIT, etc. I can also get the same result if I execute it in pgadmin (version downloaded from postgres community) which is on a different client machine but connected to the server on enterpriseDB version As EXEC proc. If you're connected to EnterpriseDB, I'd expect that. If you're connected to PostgreSQL, maybe PgAdmin is translating EXEC into a SELECT ? -- Craig Ringer
Re: [GENERAL] pg crash shortly after 9.1.1 - 9.1.2 upgrade
On 12/08/2011 12:54 AM, Tom Lane wrote:
Joseph Shraibmanj...@selectacast.net writes:
All was fine until:
LOG: statement: select _devel.cleanupEvent('10 minutes'::interval,
'false'::boolean);
ERROR: could not open file base/16406/2072097_fsm: Permission denied
That's pretty weird. What were the permissions on that file? Was it
properly owned by the postgres user?
It had no permissions at all
-- 1 postgres postgres 0 Feb 14 2005 2072097_fsm
I actually didn't notice the old date until now. This was an 8.4.x
database that I upgraded to 9.1.1 a while ago using pg_upgrade (using
the hardlink option). I still have a backup of the 8.4 database from
when I did the upgrade, and that file doesn't appear in it.
WARNING: AbortTransaction while in COMMIT state
PANIC: cannot abort transaction 7108311, it was already committed
And that's even weirder, since it implies that we tried to open the FSM
file during post-commit cleanup. The commit-record printout suggests
that we probably were trying to delete the file due to a committed DROP
TABLE operation, but I don't understand why we tried to open the file
rather than just unlink it.
Can you reproduce this?
No. Nothing was going on at the time, and it hasn't happened again (yet).
Can you show us what cleanupEvent() does?
It's from slony 2.0.7. The server wasn't in heavy use at the time, and
all that appeared in the logs as far back as I cared to look was slony
activity. That was that last line before the error.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Error while vacuuming table
Hello, When I run vacuum, I get: vacuumdb: vacuuming of database pm50 failed: ERROR: catalog is missing 1 attribute(s) for relid 4210163 How do I go about debugging this? I have googled around and tried a few things to no avail. Any pointers will be appreciated. Thanks. Nikhil -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
