Re: [GENERAL] Client SQL Tool
I don't want to replace the other tools. It's just another client for postgreSQL and there is a long road for a release. And yes, it uses .NET 4.0 and WPF. The implementation is more than a little more than simply ADO.NET. The ADO.NET objects only transfer the results to the view. In the backend the sql query is handled as a really SQL statement. But it's ok, if you dont't interested in. -Ursprüngliche Nachricht- From: Brandon Phelps Sent: Monday, November 14, 2011 11:48 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Client SQL Tool On 11/14/2011 05:42 PM, John R Pierce wrote: On 11/14/11 2:32 PM, Josh Kupershmidt wrote: How does this client compare to pgAdmin (as a graphical client) or psql (as a client in general)? its a MS Windows only program using the .NET framework, and it just executes manually entered SQL commands, displaying the results in a ADO grid object. *YAWN* Yeah no offense to the author but it looks like this app has a long way to go before it even comes close to being a drop in replacement for pgAdmin, Navicat, etc. -- 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
[GENERAL] how to adjust auto increment id offset?
I would like to implement two master db with even-odd id sharding. in mysql it is fairly easy by using the configuration: auto_increment_offset = 1 auto_increment_increment = 2 but I have searched a lot didn't find anything related to this, some users doing this via trigger like rubyrep. is there an easy way to do this? thanks!
Re: [GENERAL] how to adjust auto increment id offset?
Hi On 15 November 2011 11:44, Yan Chunlu springri...@gmail.com wrote: I would like to implement two master db with even-odd id sharding. in mysql it is fairly easy by using the configuration: auto_increment_offset = 1 auto_increment_increment = 2 but I have searched a lot didn't find anything related to this, some users doing this via trigger like rubyrep. is there an easy way to do this? thanks! http://www.postgresql.org/docs/8.1/static/sql-createsequence.html -- == If Pac-Man had affected us as kids, we'd all be running around in a darkened room munching pills and listening to repetitive music.
Re: [GENERAL] Client SQL Tool
On 11/15/2011 01:55 PM, bjo...@hillebrandar.de wrote: I don't want to replace the other tools. It's just another client for postgreSQL and there is a long road for a release. And yes, it uses .NET 4.0 and WPF. The implementation is more than a little more than simply ADO.NET. The ADO.NET objects only transfer the results to the view. In the backend the sql query is handled as a really SQL statement. But it's ok, if you dont't interested in. I believe the idea is not to pit this app against any existing application paid or free. If the idea is to learn the development platform by all means, its your own software, and you should probably go ahead. Then probably posting it here is quite immaterial here .. :) But if the idea is to get communities eyes on the application, it has to interest the community on some level. An exceptional feature, unique use, mass appeal, whatever. No offence, but the base platform isn't always a striking factor. Personally, I don't care if I have a steam-engine under the bonnet as long as it runs like a Ferrari ;) -- Robins Tharakan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] all non-PK columns from information schema
I'd like to select all column names for a specific table except those that are part of the PK. I know I need to somehow join information_schema.columns, key_column_usage and table_constraints but how? -- Best Regards, Tarlika Elisabeth Schmitz -- 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] how to adjust auto increment id offset?
On 11/15/11 12:56 AM, Bèrto ëd Sèra wrote: Hi On 15 November 2011 11:44, Yan Chunlu springri...@gmail.com mailto:springri...@gmail.com wrote: I would like to implement two master db with even-odd id sharding. in mysql it is fairly easy by using the configuration: auto_increment_offset = 1 auto_increment_increment = 2 but I have searched a lot didn't find anything related to this, some users doing this via trigger like rubyrep. is there an easy way to do this? thanks! http://www.postgresql.org/docs/8.1/static/sql-createsequence.html also see ALTER SEQUENCE. basically, you'll need to fix up every sequence (these are created automatically if you have fields of type SERIAL) on your 2nd server, ALTER SEQUENCE somesequencename INCREMENT BY 2 RESTART WITH 2; and on your 1st server, ALTER SEQUENCE somesequencename INCREMENT BY 2; do this before inserting any data. thats a fairly unusual sharding technique, how do you plan on doing queries across both sets of data? -- 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] how to adjust auto increment id offset?
On Tue, Nov 15, 2011 at 04:44:23PM +0800, Yan Chunlu wrote: I would like to implement two master db with even-odd id sharding. in mysql it is fairly easy by using the configuration: auto_increment_offset = 1 auto_increment_increment = 2 but I have searched a lot didn't find anything related to this, some users doing this via trigger like rubyrep. is there an easy way to do this? thanks! Why not adjust the underlying sequences to have different start values and to advance by 2? A -- Andrew Sullivan a...@crankycanuck.ca -- 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 installation error on CentOS
Hello, We are facing an issue while installing Postgres-9.0.1 on CentOS-5. Below is the error we are encountering - ./configure -- output checking for inflate in -lz... no configure: error: zlib library not found If you have zlib already installed, see config.log for details on the failure. It is possible the compiler isn't looking in the proper directory. Use --without-zlib to disable zlib support. Inside the config.log, below is what we see - configure:8204: checking for inflate in -lz configure:8239: gcc -o conftest -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -D_GNU_SOURCEconftest.c -lz -lreadline -ltermcap -lcrypt -ldl -lm 5 /usr/bin/ld: skipping incompatible /usr/lib/libz.a when searching for -lz /usr/bin/ld: cannot find -lz Zlib rpms are installed and below is the rpm -qa output - [root@localhost postgresql-9.0.1]# rpm -qa | grep zlib zlib-1.2.3-4.el5 jzlib-1.0.7-4jpp.1 zlib-devel-1.2.3-4.el5 zlib-1.2.3-3 Thanks VB
Re: [GENERAL] : Postgres installation error on CentOS
On 15 November 2011 12:58, Venkat Balaji venkat.bal...@verse.in wrote: Hello, We are facing an issue while installing Postgres-9.0.1 on CentOS-5. That name always makes me wonder when they're releasing PennyOS or DollarOS :P Below is the error we are encountering - ./configure -- output checking for inflate in -lz... no configure: error: zlib library not found ... /usr/bin/ld: skipping incompatible /usr/lib/libz.a when searching for -lz Apparently your installed libz doesn't provide a function that configure is checking for. Perhaps upgrading it helps, if possible? Another possibility is that configure gets pointed to an old version of zlib as the first result from LD. You could try removing that, but you probably have dependencies on it from other packages. Zlib rpms are installed and below is the rpm -qa output - [root@localhost postgresql-9.0.1]# rpm -qa | grep zlib zlib-1.2.3-4.el5 jzlib-1.0.7-4jpp.1 zlib-devel-1.2.3-4.el5 zlib-1.2.3-3 As a non-linux user this doesn't mean much to me. -- 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] PostgresSQL 8.4 to 9.0 on Windows 7
Hello all, Iam newbie here and have been trying to install postgresSQL 8.4 to 9.0 on windows 7 home premium (64).I would like to talk with tomcat 6.0.33.Sometimes it works but it does not communicate to tomcat.What could be the problem? thanks in advance cheers
[GENERAL] EXECUTE USING problem
Using PG 9.0.3, I wish to dynamically reference a column in a table
passed into a PL/PgSQL function as follows:
-- A table with some values.
DROP TABLE IF EXISTS table1;
CREATE TABLE table1 (
code INT,
descr TEXT
);
INSERT INTO table1 VALUES ('1','a');
INSERT INTO table1 VALUES ('2','b');
-- The function code.
DROP FUNCTION IF EXISTS foo (TEXT);
CREATE FUNCTION foo (tbl_name TEXT) RETURNS VOID
AS $$
DECLARE
r RECORD;
d TEXT;
BEGIN
FOR r IN
EXECUTE 'SELECT * FROM ' || tbl_name
LOOP
--SELECT r.descr INTO d; --IT WORK
EXECUTE 'SELECT ($1)' || '.descr' INTO d USING r; --DOES NOT WORK
RAISE NOTICE '%', d;
END LOOP;
END;
$$ LANGUAGE plpgsql STRICT;
-- Call foo function on table1
SELECT foo('table1');
Another post suggested EXECUTE 'SELECT $1::text::table1.descr' INTO d
USING r; but this does not work either. Can this be achieved currently?
what would be the syntax ?
Thanks in advance.
--
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] : Postgres installation error on CentOS
Hi Alban, Thanks for the reply ! I was able to resolve this issue, but, not by removing the older version of zlib (i was unable to do so due to dependencies). I did not have older version of zlib-devel installed, I did that and able to install. Regards, VB On Tue, Nov 15, 2011 at 5:56 PM, Alban Hertroys haram...@gmail.com wrote: On 15 November 2011 12:58, Venkat Balaji venkat.bal...@verse.in wrote: Hello, We are facing an issue while installing Postgres-9.0.1 on CentOS-5. That name always makes me wonder when they're releasing PennyOS or DollarOS :P Below is the error we are encountering - ./configure -- output checking for inflate in -lz... no configure: error: zlib library not found ... /usr/bin/ld: skipping incompatible /usr/lib/libz.a when searching for -lz Apparently your installed libz doesn't provide a function that configure is checking for. Perhaps upgrading it helps, if possible? Another possibility is that configure gets pointed to an old version of zlib as the first result from LD. You could try removing that, but you probably have dependencies on it from other packages. Zlib rpms are installed and below is the rpm -qa output - [root@localhost postgresql-9.0.1]# rpm -qa | grep zlib zlib-1.2.3-4.el5 jzlib-1.0.7-4jpp.1 zlib-devel-1.2.3-4.el5 zlib-1.2.3-3 As a non-linux user this doesn't mean much to me. -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest.
Re: [GENERAL] EXECUTE USING problem
Hello
2011/11/15 Graham gra...@gpmd.co.uk:
Using PG 9.0.3, I wish to dynamically reference a column in a table passed
into a PL/PgSQL function as follows:
-- A table with some values.
DROP TABLE IF EXISTS table1;
CREATE TABLE table1 (
code INT,
descr TEXT
);
INSERT INTO table1 VALUES ('1','a');
INSERT INTO table1 VALUES ('2','b');
-- The function code.
DROP FUNCTION IF EXISTS foo (TEXT);
CREATE FUNCTION foo (tbl_name TEXT) RETURNS VOID
AS $$
DECLARE
r RECORD;
d TEXT;
BEGIN
FOR r IN
EXECUTE 'SELECT * FROM ' || tbl_name
LOOP
--SELECT r.descr INTO d; --IT WORK
EXECUTE 'SELECT ($1)' || '.descr' INTO d USING r; --DOES NOT WORK
RAISE NOTICE '%', d;
END LOOP;
END;
$$ LANGUAGE plpgsql STRICT;
-- Call foo function on table1
SELECT foo('table1');
Another post suggested EXECUTE 'SELECT $1::text::table1.descr' INTO d USING
r; but this does not work either. Can this be achieved currently? what would
be the syntax ?
you cannot to do it in plpgsql :(. Try to use PLPython or PLPerl
I found a working solution, but it is ugly - only plpgsql is just not
good language for this purpose. Maybe with HStore it can be done more
cleanly
CREATE or replace FUNCTION foo (tbl_name TEXT) RETURNS VOID
AS $$
DECLARE
r RECORD;
d TEXT;
BEGIN
FOR r IN
EXECUTE 'SELECT * FROM ' || tbl_name
LOOP
EXECUTE 'SELECT (''' || replace(r::text,,'') || '''::' ||
tbl_name || ').descr' INTO d;
RAISE NOTICE '%', d;
END LOOP;
END;
$$ LANGUAGE plpgsql STRICT;
Regards
Pavel Stehule
Thanks in advance.
--
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] EXECUTE USING problem
On 15/11/2011 12:45, Graham wrote:
Using PG 9.0.3, I wish to dynamically reference a column in a table
passed into a PL/PgSQL function as follows:
-- A table with some values.
DROP TABLE IF EXISTS table1;
CREATE TABLE table1 (
code INT,
descr TEXT
);
INSERT INTO table1 VALUES ('1','a');
INSERT INTO table1 VALUES ('2','b');
-- The function code.
DROP FUNCTION IF EXISTS foo (TEXT);
CREATE FUNCTION foo (tbl_name TEXT) RETURNS VOID
AS $$
DECLARE
r RECORD;
d TEXT;
BEGIN
FOR r IN
EXECUTE 'SELECT * FROM ' || tbl_name
LOOP
--SELECT r.descr INTO d; --IT WORK
EXECUTE 'SELECT ($1)' || '.descr' INTO d USING r; --DOES NOT WORK
RAISE NOTICE '%', d;
END LOOP;
I think that everything after EXECUTE needs to be a string. Also, USING
is part of an ORDER BY clause; so you'd do:
EXECUTE 'SELECT ($1)' || '.descr INTO d ORDER BY whatever USING
some_operator';
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie
--
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] all non-PK columns from information schema
On Tue, 15 Nov 2011 09:26:35 -0600 Mike Blackwell mike.blackw...@rrd.com wrote: On Tue, Nov 15, 2011 at 02:59, Tarlika Elisabeth Schmitz postgres...@numerixtechnology.de wrote: I'd like to select all column names for a specific table except those that are part of the PK. -- Tarlika Elisabeth Schmitz I'd probably use the system catalogs. See http://www.postgresql.org/docs/9.1/interactive/catalogs.html You could do something along the lines of: select attname from pg_class t join pg_attribute on (attrelid = t.oid) where attnum 0 and not exists (select 1 from pg_constraint where conrelid = t.oid and attnum = any(conkey) and contype = 'p') and relname = 'table_of_interest' __ *Mike Blackwell | Technical Analyst, Distribution Services/Rollout Thanks for the reply, Mike. Interesting. I have previously used the information_schema for similar queries. What are the pros and cons for using either pg_catalog or information_schema? -- Best Regards, Tarlika Elisabeth Schmitz -- 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] encoding and LC_COLLATE
Hi Mark (and Adrian), As as update i've now found the same data fails on my postgres 8 which doesn't seem to have the LC_COLLATE etc setting and is just UTF-8 so i guess there is possibly just something about the way the data is getting passed in. This is the error message from postgres 9.0 with the LC_COLLATE as previously described: === ERROR: invalid byte sequence for encoding UTF8: 0xe92922 CONTEXT: COPY pointsofinterest, line 2 ** Error ** ERROR: invalid byte sequence for encoding UTF8: 0xe92922 SQL state: 22021 Context: COPY pointsofinterest, line 2 === This is the error message from the postgres 8.1 with just UTF-8 set: === ERROR: invalid UTF-8 byte sequence detected near byte 0xe9 CONTEXT: COPY pointsofinterest, line 2, column street_name: Near Café) === Does that help? Is there an easy way to check exactly what encoding an existing piece of data is in? Thanks again for your help so far... Andy From: Mark Watson-12 [via PostgreSQL] [mailto:ml-node+s1045698n4992336...@n5.nabble.com] Sent: 14 November 2011 20:29 To: LPlateAndy Subject: Re: encoding and LC_COLLATE De : [hidden email] [mailto:[hidden email]] De la part de Adrian Klaver Envoyé : 14 novembre 2011 13:03 ... Second is the data coming in actually UTF8 or some other encoding? ... Hi Andy, I have to agree with Adrian in that the data may be coming in under a different encoding. An e acute is a valid character in 1252 encoding. However, if the source computer is using, for example, code page 850, an e acute is hex(82) whereas the equivalent in 1252 is hex(e9). UTF-8 doesn't like hex(82). HTH, Mark -- Sent via pgsql-general mailing list ([hidden email]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general _ If you reply to this email, your message will be added to the discussion below: http://postgresql.1045698.n5.nabble.com/encoding-and-LC-COLLATE-tp4990415p4992336.html To unsubscribe from encoding and LC_COLLATE, click here http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_codenode=4990415code=YW5keUBjZW50cmVtYXBzLmNvLnVrfDQ5OTA0MTV8LTE3NDM2MTI2 . http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewerid=instant_html%21nabble%3Aemail.namlbase=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.InstantMailNamespacebreadcrumbs=instant+emails%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml NAML -- View this message in context: http://postgresql.1045698.n5.nabble.com/encoding-and-LC-COLLATE-tp4990415p4994810.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Re: [GENERAL] all non-PK columns from information schema
On Tue, Nov 15, 2011 at 8:00 AM, Tarlika Elisabeth Schmitz postgres...@numerixtechnology.de wrote: Interesting. I have previously used the information_schema for similar queries. What are the pros and cons for using either pg_catalog or information_schema? My understanding is that pg_catalog tables and views *can* change between major releases while the information_schema is expected to be more stable between major releases. Applications that depend upon the information_schema rather than pg_catalog are less likely to break when the PostgreSQL server is upgraded. -- Regards, Richard Broersma Jr. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Syntax To Create Table As One In Another Database
I need a pointer to the appropriate docs that show me how to specify a table in a different database. What I want is to CREATE TABLE tablename AS TABLE otherdatabasesame_tablename; but using a period (dot) to separate the source database and table name doesn't work. My searches of the 9.0.x docs have missed finding this information. TIA, Rich -- 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] encoding and LC_COLLATE
Hi Adrian/Mark Thanks again for your help, i have now got the load working by setting the encoding to WIN1252. I had been assuming i was setting it to UTF8 SET CLIENT_ENCODING TO 'WIN1252'; Andy -- View this message in context: http://postgresql.1045698.n5.nabble.com/encoding-and-LC-COLLATE-tp4990415p4994930.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] PostgresSQL 8.4 to 9.0 on Windows 7
On 11/15/11 4:45 AM, Twaha Daudi wrote: Iam newbie here and have been trying to install postgresSQL 8.4 to 9.0 on windows 7 home premium (64).I would like to talk with tomcat 6.0.33.Sometimes it works but it does not communicate to tomcat.What could be the problem? insufficient information to even begin to guess what you're missing. -- 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] Syntax To Create Table As One In Another Database
On Tue, 15 Nov 2011, David Johnston wrote: Aside from roles/users each database exists in isolation and so what you describe cannot be done. The syntax you describe something.tablename is reserved for SCHEMA usage within PostgreSQL. David, This was pointed out to me. What I did was display the schema for the table, then use it to create a similar table in the new database. Thanks, Rich -- 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] Syntax To Create Table As One In Another Database
-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Rich Shepard Sent: Tuesday, November 15, 2011 11:42 AM To: pgsql-general@postgresql.org Subject: [GENERAL] Syntax To Create Table As One In Another Database I need a pointer to the appropriate docs that show me how to specify a table in a different database. What I want is to CREATE TABLE tablename AS TABLE otherdatabasesame_tablename; but using a period (dot) to separate the source database and table name doesn't work. My searches of the 9.0.x docs have missed finding this information. TIA, Rich --- Aside from roles/users each database exists in isolation and so what you describe cannot be done. The syntax you describe something.tablename is reserved for SCHEMA usage within PostgreSQL. If you really need to copy/clone a table to another database you will need to pg_dump the table and then pg_restore it into the second database. An alternative to is, somehow, simply dump INSERT statements for all the records and then manually recreate the table in the second database and then execute the INSERTS. I use third-party software that can dump the INSERTs for me so I am unsure whether psql or pgAdmin can do the same. Replication solutions work as well - depending on the complexity and frequency of your need. David J. -- 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] Syntax To Create Table As One In Another Database
On 11/15/11 8:42 AM, Rich Shepard wrote: I need a pointer to the appropriate docs that show me how to specify a table in a different database. What I want is to CREATE TABLE tablename AS TABLE otherdatabasesame_tablename; but using a period (dot) to separate the source database and table name doesn't work. My searches of the 9.0.x docs have missed finding this information. use Schemas instead of databases if this is what you need. Combined with search_path, this will get you the exact results you're looking for. in fact, the default search_path is $USER,public, so it will look in a schema named after the current user before it looks in the default schema... -- 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] Where to get PG 9.0.5 SLES RPM's !?
No suggestions about where to get the phantom RPM's ? I've managed to locate a source RPM for 9.0.3 which I'm going to look at using with source from 9.0.5 From: David Morton davidmor...@xtra.co.nz To: pgsql-general@postgresql.org pgsql-general@postgresql.org Sent: Monday, 14 November 2011 4:18 PM Subject: [GENERAL] Where to get PG 9.0.5 SLES RPM's !? I'm desperately trying to get a hold of the latest RPM's for PostgreSQL 9.0.5 for SLES 11 SP1 x86_64 . I simply can not find these anywhere !! It seems that the good folk over at software.opensuse..org are only compiling 9.1.x now. Rather annoying to say the least for those of us who don't want to upgrade data format to keep up with bug fixes. Anyone have ideas where these can be found / built !? I don't want to start building from source if it can be avoided ...
Re: [GENERAL] syntax highlighting in emacs after \e in psql
On mån, 2011-11-14 at 08:08 -0800, MikeW wrote: When I open *.sql files in my emacs it highlights the SQL and Postgres syntax correctly. But does anybody know how to make it behave like that also after invoking \e command in psql (so that I don't need to say: M-x sql-mode each time). My .profile contains: PSQL_EDITOR=emacs; export PSQL_EDITOR. (add-to-list 'auto-mode-alist '(/psql.edit.[0-9]+\\' . sql-mode)) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Questions about EXPLAIN
Hey,
PostgreSQL 9.0
1) While comparing a simple GROUP/COUNT query I noticed that TEXT and JSON
formats identify the Top-Level Plan Node differently (GroupAggregate vs.
Aggregate). More curiosity than anything but I would have expected them to
match.
2) For the same query I was hoping to be able to get the defined alias for
the COUNT output column but instead the OUTPUT simply gives the
expression. Is there some way to get EXPLAIN to output the final column
names or, assuming that this has been discussed previously (likely), could
someone link to one or more threads with the discussion as to why it has not
been done (or give a brief synopsis).
Thanks,
David J.
QUERY PLAN (TEXT) [Sample]
GroupAggregate (cost=4.27..9.64 rows=2 width=64)
Output: sqllibrary_query_name, count(sqllibrary_query_version)
QUERY PLAN (JSON) [Sample]
[
{
Plan: {
Node Type: Aggregate,
Strategy: Sorted,
Startup Cost: 4.27,
Total Cost: 9.64,
Plan Rows: 2,
Plan Width: 64,
Output: [sqllibrary_query_name,
count(sqllibrary_query_version)],
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] how to drop function?
How do I drop a function that was created like so: create or replace function process_table (action TEXT, v_table_name varchar(100)) RETURNS BOOLEAN AS $$ DECLARE BEGIN ... END; $$ LANGUAGE plpgsql; --- I have tried various ways, but it always fails. J.V. -- 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] how to drop function?
On 11/16/2011 07:38 AM, J.V. wrote: How do I drop a function that was created like so: create or replace function process_table (action TEXT, v_table_name varchar(100)) RETURNS BOOLEAN AS $$ DECLARE BEGIN ... END; $$ LANGUAGE plpgsql; --- I have tried various ways, but it always fails. DROP FUNCTION process_table (action TEXT, v_table_name varchar(100)); -- 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] how to drop function?
DROP FUNCTION process_table; should work. On Tue, Nov 15, 2011 at 11:38 PM, J.V. jvsr...@gmail.com wrote: How do I drop a function that was created like so: create or replace function process_table (action TEXT, v_table_name varchar(100)) RETURNS BOOLEAN AS $$ DECLARE BEGIN ... END; $$ LANGUAGE plpgsql; --- I have tried various ways, but it always fails. J.V. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/**mailpref/pgsql-generalhttp://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to drop function?
On Tuesday, November 15, 2011 3:56:32 pm Rebecca Clarke wrote: DROP FUNCTION process_table; should work. Actually no, for the following reason:( http://www.postgresql.org/docs/9.0/interactive/sql-dropfunction.html DROP FUNCTION removes the definition of an existing function. To execute this command the user must be the owner of the function. The argument types to the function must be specified, since several different functions can exist with the same name and different argument lists -- 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] how to drop function?
On Tue, 15 Nov 2011 16:38:20 -0700 J.V. jvsr...@gmail.com wrote: How do I drop a function that was created like so: create or replace function process_table (action TEXT, v_table_name varchar(100)) RETURNS BOOLEAN AS $$ DECLARE BEGIN ... END; $$ LANGUAGE plpgsql; --- I have tried various ways, but it always fails. J.V. test=# begin; create or replace function process_table ( action TEXT, v_table_name varchar(100) ) RETURNS BOOLEAN AS $$ DECLARE BEGIN return true; END; $$ LANGUAGE plpgsql; drop function process_table ( action TEXT, v_table_name varchar(100) ); commit; BEGIN CREATE FUNCTION DROP FUNCTION COMMIT test=# Repeat just the input parameters. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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] how to drop function?
this did not work. On 11/15/2011 4:56 PM, Craig Ringer wrote: On 11/16/2011 07:38 AM, J.V. wrote: How do I drop a function that was created like so: create or replace function process_table (action TEXT, v_table_name varchar(100)) RETURNS BOOLEAN AS $$ DECLARE BEGIN ... END; $$ LANGUAGE plpgsql; --- I have tried various ways, but it always fails. DROP FUNCTION process_table (action TEXT, v_table_name varchar(100)); -- 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] how to drop function?
this does not work. On 11/15/2011 4:56 PM, Rebecca Clarke wrote: DROP FUNCTION process_table; should work. On Tue, Nov 15, 2011 at 11:38 PM, J.V. jvsr...@gmail.com mailto:jvsr...@gmail.com wrote: How do I drop a function that was created like so: create or replace function process_table (action TEXT, v_table_name varchar(100)) RETURNS BOOLEAN AS $$ DECLARE BEGIN ... END; $$ LANGUAGE plpgsql; --- I have tried various ways, but it always fails. J.V. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org mailto:pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to drop function?
the drop function works when running from a pgAdmin III Sql window but when I try to do from the command line and script it: psql -h $PGHOST -p $PGPORT -d $PGDATABASE -U $PGUSER -c *drop function * the above fails. It does however work with functions with no params or a single param. It seems to get hung up on the comma and the extra set of parenthesis On 11/15/2011 5:01 PM, Ivan Sergio Borgonovo wrote: On Tue, 15 Nov 2011 16:38:20 -0700 J.V.jvsr...@gmail.com wrote: How do I drop a function that was created like so: create or replace function process_table (action TEXT, v_table_name varchar(100)) RETURNS BOOLEAN AS $$ DECLARE BEGIN ... END; $$ LANGUAGE plpgsql; --- I have tried various ways, but it always fails. J.V. test=# begin; create or replace function process_table ( action TEXT, v_table_name varchar(100) ) RETURNS BOOLEAN AS $$ DECLARE BEGIN return true; END; $$ LANGUAGE plpgsql; *drop function process_table ( action TEXT, v_table_name varchar(100) );* commit; BEGIN CREATE FUNCTION DROP FUNCTION COMMIT test=# Repeat just the input parameters.
Re: [GENERAL] how to drop function?
On Nov 15, 2011, at 20:24, J.V. jvsr...@gmail.com wrote: this did not work. On 11/15/2011 4:56 PM, Craig Ringer wrote: On 11/16/2011 07:38 AM, J.V. wrote: How do I drop a function that was created like so: create or replace function process_table (action TEXT, v_table_name varchar(100)) RETURNS BOOLEAN AS $$ DECLARE BEGIN ... END; $$ LANGUAGE plpgsql; --- I have tried various ways, but it always fails. DROP FUNCTION process_table (action TEXT, v_table_name varchar(100)); -- Craig Ringer If you are going to claim something doesn't work it really helps to provide the clues that lead you to that conclusion. Specifically, what error message(s) are you seeing? The parameter names and the (100) are both optional so try removing them and see what happens. David J.
[GENERAL] deferring pk constraint
I have a table with existing data for which I need to: 1) drop the single primary key column (int4) 2) recreate the column with the pk (not null) constraint deferred 3) repopulate the column from a sequence 4) enable the constraint When I issue this command to add the column: alter table table_name add column id INT4 NOT NULL; I get an error saying: ERROR: column id contains null values. Is there a way to issue the alter table... command and defer the constraint (did not see in online docs) and then at some point enable it? What would be the best approach here? -- 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] how to drop function?
On Tue, Nov 15, 2011 at 6:48 PM, J.V. jvsr...@gmail.com wrote: the drop function works when running from a pgAdmin III Sql window but when I try to do from the command line and script it: psql -h $PGHOST -p $PGPORT -d $PGDATABASE -U $PGUSER -c drop function the above fails. What's the rest of that line look like? What error do you get? -- 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] deferring pk constraint
On Tue, 15 Nov 2011 18:56:37 -0700, J.V. wrote:
I have a table with existing data for which I need to:
1) drop the single primary key column (int4)
2) recreate the column with the pk (not null) constraint deferred
3) repopulate the column from a sequence
4) enable the constraint
When I issue this command to add the column:
alter table table_name add column id INT4 NOT NULL;
I get an error saying:
ERROR: column id contains null values.
Is there a way to issue the alter table... command and defer the
constraint (did not see in online docs)
and then at some point enable it?
What would be the best approach here?
Create the sequence first and create the new column with a default.
alter table foo drop constraint foo_pkey;
create sequence foo_id_seq;
alter table foo add id bigint default nextval('foo_id_seq'::regclass);
alter sequence foo_id_seq owned by foo.id;
alter table foo add primary key (id) deferrable;
Sequences use bigint, rather than int4, so your
new key column should be bigint.
--
nw
--
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] how to drop function?
On 11/15/11 5:48 PM, J.V. wrote: the drop function works when running from a pgAdmin III Sql window but when I try to do from the command line and script it: psql -h $PGHOST -p $PGPORT -d $PGDATABASE -U $PGUSER -c *drop function * the above fails. can you please give the complete command line and the error message you get instead of just saying 'fails' ? btw, if in fact PGHOST, PGPORT PGDATABASE and PGUSER are set in the environment, you don't need to specify any of those on the command line. but if those are just placeholders for actual names, well, we can't tell that from here. -- 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] how to adjust auto increment id offset?
thanks a lot for the tip! sorry for used the wrong word, it is just multi-master but not sharding, I would like to setup two master server across two datacenter. one's id increased by 1, and the other by 2. so I could have a queue sync the record in the background by myself. kind of a dumb way but it seems they only choice for me, the delay within several minutes is acceptable. On Tue, Nov 15, 2011 at 5:12 PM, John R Pierce pie...@hogranch.com wrote: On 11/15/11 12:56 AM, Bèrto ëd Sèra wrote: Hi On 15 November 2011 11:44, Yan Chunlu springri...@gmail.com mailto: springri...@gmail.com** wrote: I would like to implement two master db with even-odd id sharding. in mysql it is fairly easy by using the configuration: auto_increment_offset = 1 auto_increment_increment = 2 but I have searched a lot didn't find anything related to this, some users doing this via trigger like rubyrep. is there an easy way to do this? thanks! http://www.postgresql.org/**docs/8.1/static/sql-**createsequence.htmlhttp://www.postgresql.org/docs/8.1/static/sql-createsequence.html also see ALTER SEQUENCE. basically, you'll need to fix up every sequence (these are created automatically if you have fields of type SERIAL) on your 2nd server, ALTER SEQUENCE somesequencename INCREMENT BY 2 RESTART WITH 2; and on your 1st server, ALTER SEQUENCE somesequencename INCREMENT BY 2; do this before inserting any data. thats a fairly unusual sharding technique, how do you plan on doing queries across both sets of data? -- 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-generalhttp://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to adjust auto increment id offset?
On Tue, Nov 15, 2011 at 9:03 PM, Yan Chunlu springri...@gmail.com wrote: thanks a lot for the tip! sorry for used the wrong word, it is just multi-master but not sharding, I would like to setup two master server across two datacenter. one's id increased by 1, and the other by 2. so I could have a queue sync the record in the background by myself. kind of a dumb way but it seems they only choice for me, the delay within several minutes is acceptable. you might want to look at either using a larger skip, like 10 or 20, so you can add more servers at a later date if you need to. The other way is to start each sequence at some huge offset like 2Billion and be sure to use a bigint not a regular int. -- 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] partitioning a dataset + employing hysteresis condition
On 14/11/11 18:35, Amit Dor-Shifer wrote: On Mon, Nov 14, 2011 at 4:29 PM, Amit Dor-Shifer amit.dor.shi...@gmail.com mailto:amit.dor.shi...@gmail.com wrote: Hi, I've got this table: create table phone_calls ( start_time timestamp, device_id integer, term_status integer ); [...] 3 points POINT 1: I should have given the results of my attempt... [...] device_id --- 2 40 50 60 (4 rows) POINT 2: I also realized I left of a condition in the HAVBING part HAVING max(pc1.start_time) = min(pc1.start_time) + interval '2 minute' I think it should be HAVING max(pc1.start_time) = min(pc1.start_time) + interval '2 minute' AND pc1.term_status = 2 POINT 3: Timestamps should almost always be stored with a time zone (using timestamptz rather than just timestamp) -- or you will have problems when Summer time ends or begins, and using timestamptz allows for datetime to be displayed currectly in different locales. Regards, Gavin P.S. Since my post has not shown up yet, I had to attach to its parent! (I won't offer my first born to get direct posting rights, as his wife may object - besides which, it is probably illegal in my jurisdiction!)
Re: [GENERAL] partitioning a dataset + employing hysteresis condition
On 14/11/11 18:35, Amit Dor-Shifer wrote: On Mon, Nov 14, 2011 at 4:29 PM, Amit Dor-Shifer amit.dor.shi...@gmail.com mailto:amit.dor.shi...@gmail.com wrote: Hi, I've got this table: create table phone_calls ( start_time timestamp, device_id integer, term_status integer ); It describes phone call events. A 'term_status' is a sort-of an exit status for the call, whereby a value != 0 indicates some sort of error. Given that, I wish to retrieve data on devices with a persisting error on them, of a specific type. I.E. that their last term_status was, say 2. I'd like to employ some hysteresis on the query: only consider a device as errorring if: 1. the last good (0) term_status pre-dates a bad (2) term_status. 2. it has at least N bad term_status events following the last good one. 3. The time span between the first bad term_status event and the last one is = T minutes For instance, w/the following data set: INSERT INTO phone_calls(start_time, device_id, term_status) VALUES (now() - interval '10 minutes', 1, 2, 0); INSERT INTO phone_calls(start_time, device_id, term_status) VALUES (now() - interval '9 minutes', 1, 2, 1); INSERT INTO phone_calls(start_time, device_id, term_status) VALUES (now() - interval '7 minutes', 1, 2, 1); INSERT INTO phone_calls(start_time, device_id, term_status) VALUES (now() - interval '6 minutes', 1, 2, 1); INSERT INTO phone_calls(start_time, device_id, term_status) VALUES (now() - interval '5 minutes', 1, 2, 0); INSERT INTO phone_calls(start_time, device_id, term_status) VALUES (now() - interval '4 minutes', 1, 2, 2); INSERT INTO phone_calls(start_time, device_id, term_status)VALUES (now() - interval '3 minutes', 1, 2, 2); INSERT INTO phone_calls(start_time, device_id, term_status)VALUES (now() - interval '2 minutes', 1, 2, 2); with N=3, T=3 The query should return device_id 2 as errorring, as it registered 3 bad events for at least 3 minutes. I assume some partitioning needs to be employed here, but am not very sure-footed on the subject. Would appreciate some guidance. 10x, ... fixed data set: INSERT INTO phone_calls(start_time, device_id, term_status) VALUES (now() - interval '10 minutes', 2, 0); INSERT INTO phone_calls(start_time, device_id, term_status) VALUES (now() - interval '9 minutes', 2, 1); INSERT INTO phone_calls(start_time, device_id, term_status) VALUES (now() - interval '7 minutes', 2, 1); INSERT INTO phone_calls(start_time, device_id, term_status) VALUES (now() - interval '6 minutes', 2, 1); INSERT INTO phone_calls(start_time, device_id, term_status) VALUES (now() - interval '5 minutes', 2, 0); INSERT INTO phone_calls(start_time, device_id, term_status) VALUES (now() - interval '4 minutes', 2, 2); INSERT INTO phone_calls(start_time, device_id, term_status)VALUES (now() - interval '3 minutes', 2, 2); INSERT INTO phone_calls(start_time, device_id, term_status)VALUES (now() - interval '2 minutes', 2, 2); Hi , This is my attempt... DROP TABLE IF EXISTS phone_call; CREATE TABLE phone_call ( device_id int NOT NULL, start_time timestamptz NOT NULL, term_status int NOT NULL, PRIMARY KEY (device_id, start_time, term_status) ); INSERT INTO phone_call ( device_id, start_time, term_status ) VALUES (10, '20100701T151433', 0), (20, '20100701T151533', 0), (20, '20100701T151633', 2), (30, '20100701T151433', 0), (30, '20100701T151533', 2), (30, '20100701T151633', 2), (40, '20100701T004022', 0), (40, '20100701T004122', 2), (40, '20100701T004622', 2), (40, '20100701T010022', 2), (40, '20100701T012122', 2), (50, '20100701T12', 0), (50, '20100701T120100', 2), (50, '20100701T120200', 2), (50, '20100701T120300', 2), (60, '20100701T09', 0), (60, '20100701T090200', 2), (60, '20100701T10', 0), (60, '20100701T100100', 2), (60, '20100701T100200', 2), (60, '20100701T100300', 2), (60, '20100701T101000', 2), (60, '20100701T102000', 2), (60, '20100701T104000', 2), (60, '20100701T105000', 2), (60, '20100701T105200', 2), (60, '20100701T105600', 2), (60, '20100701T500300', 0), (60, '20100701T501400', 2); INSERT INTO phone_call(start_time, device_id, term_status) VALUES (now() - interval '10 minutes', 2, 0); INSERT INTO phone_call(start_time, device_id, term_status) VALUES (now() - interval '9 minutes', 2, 1); INSERT INTO phone_call(start_time, device_id, term_status) VALUES (now() - interval '7 minutes', 2, 1); INSERT INTO phone_call(start_time, device_id, term_status) VALUES (now() - interval '6 minutes', 2, 1); INSERT INTO phone_call(start_time, device_id, term_status) VALUES (now() - interval '5 minutes', 2, 0); INSERT INTO phone_call(start_time, device_id, term_status) VALUES (now()
Re: [GENERAL] partitioning a dataset + employing hysteresis condition
On Nov 15, 2011, at 15:28, Gavin Flower gavinflo...@archidevsys.co.nz wrote: On 14/11/11 18:35, Amit Dor-Shifer wrote: On Mon, Nov 14, 2011 at 4:29 PM, Amit Dor-Shifer amit.dor.shi...@gmail.com wrote: Hi, I've got this table: create table phone_calls ( start_time timestamp, device_id integer, term_status integer ); It describes phone call events. A 'term_status' is a sort-of an exit status for the call, whereby a value != 0 indicates some sort of error. Given that, I wish to retrieve data on devices with a persisting error on them, of a specific type. I.E. that their last term_status was, say 2. I'd like to employ some hysteresis on the query: only consider a device as errorring if: 1. the last good (0) term_status pre-dates a bad (2) term_status. 2. it has at least N bad term_status events following the last good one. 3. The time span between the first bad term_status event and the last one is = T minutes For instance, w/the following data set: Alternative thought, Have a Boolean field which is set to true for non-zero entries and false for zeros. Upon entering a zero into the table, for a given device, set all currently true records to false. Combine with a partial index on the true and you can quickly get a listing of all devices in error mode and all the recent error entries. David J.
