[GENERAL] Ungooglable error message when running initdb: Symbol not found: _check_encoding_conversion_args
Hello there, I'm configuring with `./configure --prefix=/usr/local/Cellar/postgresql/8.4.0`, without sudo. I hawe chowned /usr/local so that I don't need to sudo it. I'm getting the following error when running `initdb` after successfully compiling postgresql Symbol not found: _check_encoding_conversion_args Here's the full output. augu...@honk:~$ initdb -D /usr/local/Cellar/postgresql/8.4.0/defaultdb The files belonging to this database system will be owned by user augustl. This user must also own the server process. The database cluster will be initialized with locales COLLATE: C CTYPE:UTF-8 MESSAGES: C MONETARY: C NUMERIC: C TIME: C The default database encoding has accordingly been set to UTF8. initdb: could not find suitable text search configuration for locale UTF-8 The default text search configuration will be set to simple. creating directory /usr/local/Cellar/postgresql/8.4.0/defaultdb ... ok creating subdirectories ... ok selecting default max_connections ... 20 selecting default shared_buffers ... 2400kB creating configuration files ... ok creating template1 database in /usr/local/Cellar/postgresql/8.4.0/defaultdb/base/1 ... ok initializing pg_authid ... ok initializing dependencies ... ok creating system views ... ok loading system objects' descriptions ... ok creating conversions ... FATAL: could not load library /usr/local/Cellar/postgresql/8.4.0/lib/ascii_and_mic.so: dlopen(/usr/local/Cellar/postgresql/8.4.0/lib/ascii_and_mic.so, 10): Symbol not found: _check_encoding_conversion_args Referenced from: /usr/local/Cellar/postgresql/8.4.0/lib/ascii_and_mic.so Expected in: /usr/local/Cellar/postgresql/8.4.0/bin/postgres in /usr/local/Cellar/postgresql/8.4.0/lib/ascii_and_mic.so STATEMENT: CREATE OR REPLACE FUNCTION ascii_to_mic (INTEGER, INTEGER, CSTRING, INTERNAL, INTEGER) RETURNS VOID AS '$libdir/ascii_and_mic', 'ascii_to_mic' LANGUAGE C STRICT; child process exited with exit code 1 initdb: removing data directory /usr/local/Cellar/postgresql/8.4.0/defaultdb augu...@honk:~$ I found something on google about the file ascii_and_mic.so not existing, but that's not the case here; the file does indeed exist. Googling _check_encoding_conversion_args doesn't yield any results. My system is OS X 10.6 (Snow Leopard). I'm working with http://ftp9.us.postgresql.org/pub/mirrors/postgresql/source/v8.4.0/postgresql-8.4.0.tar.gz .
[GENERAL] PostgreSQL Live CD based on CentOS 5.3 and PG 8.4 released
You wanted it, and here we go: I released initial version of my PostgreSQL 8.4 live CD, which is based on CentOS 5.3. It includes the PostgreSQL related packages that I build on http://yum.pgsqlrpms.org , along with PostgreSQL 8.4.0. Details are here: http://yum.pgsqlrpms.org/livecd.php http://www.pglivecd.org This live CD has current versions of many software, like pgAdmin III, phpPgAdmin, Apache, PHP, GNOME, Pidgin, Firefox etc. Kickstart file is configurable, so you can also create your own PostgreSQL Live CD's fairly easily, if you have a CentOS 5.3 machine around, as described in here: https://projects.centos.org/trac/livecd/ Please let me know if you have any questions regarding this live CD. Sincerely, -- Devrim GÜNDÜZ, RHCE Command Prompt - http://www.CommandPrompt.com devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org signature.asc Description: This is a digitally signed message part
[GENERAL] creating array of integer[] out of query - how?
Hello,
if I use this statement:
postgres=# select array[[2,3],[3,4]];
array
---
{{2,3},{3,4}}
- the result looks for me as an array of integer-arrays
now I try:
select array(
select a from
(
select array[2,3] as a
union
select array[3,4] as a
) x);
and the result is:
FEHLER: could not find array type for datatype integer[]
Is there a bug in my thinking that array[[2,3],[3,4]] really constitutes an
array of integer[],
or is there any cast missing,
or is a bug anywhere else?
Best wishes,
Harald
--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
no fx, no carrier pigeon
-
LASIK good, steroids bad?
Re: [GENERAL] creating array of integer[] out of query - how?
On Wed, Sep 02, 2009 at 10:34:31AM +0200, Massa, Harald Armin wrote: Is there a bug in my thinking that array[[2,3],[3,4]] really constitutes an array of integer[], no. array[[2,3],[3,4]] is 2 dimensional array of integers. not array of arrays of integers. Best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007 -- 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] creating array of integer[] out of query - how?
On Wed, Sep 02, 2009 at 10:34:31AM +0200, Massa, Harald Armin wrote:
postgres=# select array[[2,3],[3,4]];
array
---
{{2,3},{3,4}}
- the result looks for me as an array of integer-arrays
No, as depesz says it's not doing that. Depending on what you want out
you can get most of the way by having an array of ROWs that contain an
array of integers. You just need to change:
select array(
select a from (
select array[2,3] as a
union
select array[3,4] as a ) x);
to return x instead of a in the inner select. Something like:
select array(
select x from (
select array[2,3] as a
union
select array[3,4] as a ) x);
getting the resulting tuples out again is a bit of a struggle and you
may be better off with using a custom type. Have a look at CREATE
TYPE[1] for this.
--
Sam http://samason.me.uk/
[1] http://www.postgresql.org/docs/current/static/sql-createtype.html
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL Conference 2009 Japan
Hi, I've added a wiki page with some information you might find helpful, if you are attending the PostgreSQL Conference 2009 in Japan. However, I've never been to Tokyo before, so please feel free to correct and add better links, hints and recommendations: http://wiki.postgresql.org/wiki/PostgreSQL_Conference_2009_Japan I'd personally like to stay in a hotel with other fellow hackers, as those late night discussions tend to be very inspiring as well. So, what hotel do you plan to stay at? Regards Markus Wanner -- 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] creating array of integer[] out of query - how?
Sam, No, as depesz says it's not doing that. Depending on what you want out you can get most of the way by having an array of ROWs that contain an array of integers. You just need to change: the sad thing is: select array( select x from ( select array[2,3] as a union select array[3,4] as a ) x); ERROR: could not find array type for datatype record ... I remember being there before :( arrays of rows are also not available. To all: is there a deeper reason why there is no array type for datatype record available? [1] http://www.postgresql.org/docs/current/static/sql-createtype.html Thanks for the hint with CREATE TYPE, especially the lines Whenever a user-defined type is created, PostgreSQL automatically creates an associated array type, fills me with joy. ;) Thanks to depesz you, Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - LASIK good, steroids bad?
Re: [GENERAL] creating array of integer[] out of query - how?
On Wed, Sep 02, 2009 at 11:50:38AM +0200, Massa, Harald Armin wrote: select array( select x from ( select array[2,3] as a union select array[3,4] as a ) x); ERROR: could not find array type for datatype record ... I remember being there before :( arrays of rows are also not available. Doh, sorry I forgot that that's an 8.4 only. Before that you must create your own composite type. To all: is there a deeper reason why there is no array type for datatype record available? Not enough demand :) [1] http://www.postgresql.org/docs/current/static/sql-createtype.html Thanks for the hint with CREATE TYPE, especially the lines Whenever a user-defined type is created, PostgreSQL automatically creates an associated array type, fills me with joy. ;) Try: CREATE TYPE intarr AS (arr int[]); SELECT array( SELECT x::intarr FROM ( SELECT array[2,3] UNION ALL SELECT array[3,4]) x(a)); and it should do the right thing in 8.3. -- Sam http://samason.me.uk/ -- 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] Ungooglable error message when running initdb: Symbol not found: _check_encoding_conversion_args
On Wed, 2009-09-02 at 09:52 +0200, August Lilleaas wrote: augu...@honk:~$ initdb -D /usr/local/Cellar/postgresql/8.4.0/defaultdb Is initdb under /usr/local/Cellar/postgresql/8.4.0/bin, or is there another one in $PATH? -- Devrim GÜNDÜZ, RHCE Command Prompt - http://www.CommandPrompt.com devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org signature.asc Description: This is a digitally signed message part
[GENERAL] PosgreSQL Service does not Start In Vista
Hi all. We have included PostgreSQL 8.3(exe) installation in the installation process of our software, and we are experimenting some errors, mainly related with Windows Vista. The problem is that the installation of PosgreSQL seems to be correct, but the service is not started afterwards. I tried to start it manually but takes a lot of time and finally a windows error appears telling that the PostgreSQL Database server 8.3 service on Local Computer Started and then stopped. Some services stop automatically if they have no work to do I've read something in the forum related with the pg_hba.conf configuration file. We distribute this file in the client machines with the follOwing lines: # IPv4 local connections: hostall all 127.0.0.1/32 md5 hostDATABASE_NAME all 0.0.0.0 0.0.0.0 md5 # IPv6 local connections: #hostall all ::1/128 md5 Do you know where could be the problem? Thanks in advance for any suggestion. Best, -- View this message in context: http://www.nabble.com/PosgreSQL-Service-does-not-Start-In-Vista-tp25255182p25255182.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] PosgreSQL Service does not Start In Vista
On 02/09/2009 11:39, Inigo Barandiaran wrote: PosgreSQL seems to be correct, but the service is not started afterwards. I tried to start it manually but takes a lot of time and finally a windows error appears telling that the PostgreSQL Database server 8.3 service on Local Computer Started and then stopped. Some services stop automatically if they have no work to do There should be more detail in the Postgres logs - have a look there and see what it says. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- 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] Join efficiency
On Wed, Sep 02, 2009 at 02:31:46PM +0900, tanjunhua wrote: I have the trouble that it cost me a lot of time when execute the select syntax. the following is the select syntax and analyze result. EXPLAIN ANALYZE SELECT count(Id) FROM (SELECT DISTINCT t1.Id AS Id FROM tab_main t1, tab_user t2, tab_property t3 WHERE (t1.uid = 2 AND t1.status = 21 AND t1.bpassword = 0 AND t1.realdelflag = 0 AND (t1.kind= 1 OR (t1.kind = 0 AND (t1.delflag 0 OR (t2.uid = 2 AND (t2.printauth = 2 OR t2.printauth = 3) AND t2.bprtpermit = 0 AND t3.id = t1.id AND (t3.mode = 0 OR t3.mode = 1))) subt0; That WHERE clause is far too complicated to allow PG's optimizer to have a chance. The Nested Loop running over sequential scans is a sign that things aren't going to work out well. OR clauses are the awkward one, as you've got one at the top of your WHERE clause it's going to force PG to do slow things. It looks somewhat strange as well, do you really want to join *every* row in tab_main to *every* row in tab_user when tab_main.kind doesn't equal zero? Maybe if you could describe what you want to do in English then the query would make a bit more sense. -- Sam http://samason.me.uk/ -- 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] PosgreSQL Service does not Start In Vista
Thanks Raymond. Where can I find that log? I took a look in the bin folder where the service, and there is no log there. Thanks in advance! Raymond O'Donnell wrote: On 02/09/2009 11:39, Inigo Barandiaran wrote: PosgreSQL seems to be correct, but the service is not started afterwards. I tried to start it manually but takes a lot of time and finally a windows error appears telling that the PostgreSQL Database server 8.3 service on Local Computer Started and then stopped. Some services stop automatically if they have no work to do There should be more detail in the Postgres logs - have a look there and see what it says. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- View this message in context: http://www.nabble.com/PosgreSQL-Service-does-not-Start-In-Vista-tp25255182p25255298.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] PosgreSQL Service does not Start In Vista
Sorry, I've just found it in: data\pg_log I'm going to take a look. Thanks again :) Inigo Barandiaran wrote: Thanks Raymond. Where can I find that log? I took a look in the bin folder where the service, and there is no log there. Thanks in advance! Raymond O'Donnell wrote: On 02/09/2009 11:39, Inigo Barandiaran wrote: PosgreSQL seems to be correct, but the service is not started afterwards. I tried to start it manually but takes a lot of time and finally a windows error appears telling that the PostgreSQL Database server 8.3 service on Local Computer Started and then stopped. Some services stop automatically if they have no work to do There should be more detail in the Postgres logs - have a look there and see what it says. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- View this message in context: http://www.nabble.com/PosgreSQL-Service-does-not-Start-In-Vista-tp25255182p25255321.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] Ungooglable error message when running initdb: Symbol not found: _check_encoding_conversion_args
It is in /usr/local/Cellar/postgresql/8.4.0/bin/, but `which initdb` returns /usr/local/bin/initdb. The /usr/local/bin one is a symlink to the /usr/local/Cellar/postgresql/8.4.0/bin one. Running /usr/local/Cellar/postgresql/8.4.0/bin/initdb -D /usr/local/Cellar/postgresql/8.4.0/defaultdb yields the same results. Does this bring us any closer to a solution? 2009/9/2 Devrim GÜNDÜZ dev...@gunduz.org On Wed, 2009-09-02 at 09:52 +0200, August Lilleaas wrote: augu...@honk:~$ initdb -D /usr/local/Cellar/postgresql/8.4.0/defaultdb Is initdb under /usr/local/Cellar/postgresql/8.4.0/bin, or is there another one in $PATH? -- Devrim GÜNDÜZ, RHCE Command Prompt - http://www.CommandPrompt.com devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org -- August Lilleaas
Re: [GENERAL] PosgreSQL Service does not Start In Vista
Inigo Barandiaran, 02.09.2009 12:39: We have included PostgreSQL 8.3(exe) installation in the installation process of our software, and we are experimenting some errors, mainly related with Windows Vista. The problem is that the installation of PosgreSQL seems to be correct, but the service is not started afterwards. I tried to start it manually but takes a lot of time and finally a windows error appears telling that the PostgreSQL Database server 8.3 service on Local Computer Started and then stopped. Some services stop automatically if they have no work to do Most probably it is a problem with the access privileges for the data directory. Make sure the postgres service account has full write privileges on the data directory. If your data directory is located in Program Files this is not a surprise because that directory is write protected for regular users (for a good reason) Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Add a serial column to a table based on a sort clause
I have a table, which has a creation_ts (timestamp) column, but does not
have a id (serial) column. I want to add such a one, but, AFAIK, if I enter
ALTER TABLE table ADD COLUMN id serial
it will randomly put the sequence numbers.
I wrote a function, which uses a cursor and UPDATE WHERE CURRENT OF, and
it works, but is there any other, more elegant, way?
CREATE OR REPLACE FUNCTION newsfeed_seq_add_cursor
() RETURNS void AS $$
DECLARE
curs refcursor;
rec record;
BEGIN
create sequence seq;
ALTER TABLE table ADD COLUMN id int;
OPEN curs FOR SELECT * FROM table ORDER BY creation_ts FOR UPDATE;
FETCH curs INTO rec;
WHILE FOUND IS TRUE
LOOP
UPDATE table SET id = nextval('seq') WHERE CURRENT OF curs;
END LOOP;
ALTER TABLE table ALTER COLUMN id SET NOT NULL, ALTER COLUMN id
SET DEFAULT nextval('seq');
END;
$$ language plpgsql;
--
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] PosgreSQL Service does not Start In Vista
Thanks Thomas!. That sounds very interesting. How can I set privileges for writing in data directory for the postgres user account?. Or is it very to directly install posgreSQL out of Program Files Directory? Thanks in advance! I've check Thomas Kellerer wrote: Inigo Barandiaran, 02.09.2009 12:39: We have included PostgreSQL 8.3(exe) installation in the installation process of our software, and we are experimenting some errors, mainly related with Windows Vista. The problem is that the installation of PosgreSQL seems to be correct, but the service is not started afterwards. I tried to start it manually but takes a lot of time and finally a windows error appears telling that the PostgreSQL Database server 8.3 service on Local Computer Started and then stopped. Some services stop automatically if they have no work to do Most probably it is a problem with the access privileges for the data directory. Make sure the postgres service account has full write privileges on the data directory. If your data directory is located in Program Files this is not a surprise because that directory is write protected for regular users (for a good reason) Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- View this message in context: http://www.nabble.com/PosgreSQL-Service-does-not-Start-In-Vista-tp25255182p25256915.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] Join efficiency
thanks for your response. Maybe if you could describe what you want to do in English then the query would make a bit more sense. I just want those records as the below rule: 1. the record of which uid is 2, status is more than 20, bpassword is 0 and realdelflag is 0 in tab_main; 1.1 the record of which kind is 1 in those that filtered through step1; 1.2 the record of which kind is 0 in those that filtered through step1; 1.2.1 the record of which delflag doesn't equal 0 in those filtered through step1.2; 1.2.2 the record of which uid equal 2, printauth equal 2 or 3 and bprtpermit equal 0 in tab_user left join those filtered through step1.2; 1.2.2.1 the record of which mode equal to 0 or 1 in tab_property and left join those filtered through step1.2.2 using id; such as the following data, the expect result is 1 record and detail information is: id | uid | status| bpassword| realdelflag| delflag| kind ---+-++-+-+-+- 39731 | 2 | 21 | 0 | 0 | 0 | 1 tab_main: id | uid | status| bpassword| realdelflag| delflag| kind ---+-++-+-+-+- 39752 | 1 | 0 | 0 | 0 | 0 | 2 39751 | 1 | 21 | 0 | 0 | -1 | 2 39750 | 2 | 0 | 1 | 0 | 0 | 2 39749 | 2 | 21 | 1 | 0 | -1 | 2 39748 | 2 | 0 | 1 | 0 | 0 | 2 39731 | 2 | 21 | 0 | 0 | 0 | 1 39728 | 2 | 1 | 1 | 0 | 0 | 1 39727 | 2 | 1 | 0 | 0 | 0 | 1 39710 | 0 | 0 | 0 | 0 | 0 | 1 39709 | 2 | 0 | 1 | 0 | 0 | 1 39681 | 0 | 0 | 0 | 0 | -1 | 0 4333 | 0 | 0 | 0 | 0 | -1 | 0 tab_user: uid | printauth| bprtpermit -++ 1 | 1 | 1 2 | 2 | 0 3 | 1 | 1 tab_property: id | mode ---+--- 39731 | 1 39728 | 4 39727 | 4 39710 | 1 39709 | 0 That WHERE clause is far too complicated to allow PG's optimizer to have a chance. The Nested Loop running over sequential scans is a sign that things aren't going to work out well. OR clauses are the awkward one, as you've got one at the top of your WHERE clause it's going to force PG to do slow things. It is my first time to use database in practise, could you give me more detail? such as how to decision the WHERE clause complication? how to make the best choice by analyze result? Would you supply some documents about postgresql performance? bese wishes. winsea -- 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] creating array of integer[] out of query - how?
Sam,
To all: is there a deeper reason why there is no array type for datatype
record available?
Not enough demand :)
seams reasonable :)
Try:
CREATE TYPE intarr AS (arr int[]);
SELECT array(
SELECT x::intarr FROM (
SELECT array[2,3]
UNION ALL
SELECT array[3,4]) x(a));
and it should do the right thing in 8.3.
not exactly :)
ibox=# CREATE TYPE intarr AS (arr int[]);
CREATE TYPE
ibox=# SELECT array(
SELECT x::intarr FROM (
SELECT array[2,3]
UNION ALL
SELECT array[3,4]) x(a));
?column?
---
{(\{2,3}\),(\{3,4}\)}
(1 Zeile)
the result seems to be an array with two strings containing escaped
string-represenations of arrays :)
I guess I will try to solve my challenge without arrays of arrays or records
:)
Thanks for trying,
Harald
--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
no fx, no carrier pigeon
-
LASIK good, steroids bad?
[GENERAL] PL/SQL unset custom variable
(I'm using Postgres 8.3) I have a trigger that references a custom variable. Most of the time this custom variable is set, and I have no problems. However, in certain corner cases the custom variable is not set and the trigger fails. Is there a way to either test if the custom variable is set, or to specify a global default for the custom variable ? Thanks, GTG -- 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] creating array of integer[] out of query - how?
On Wed, Sep 02, 2009 at 03:47:53PM +0200, Massa, Harald Armin wrote:
ibox=# CREATE TYPE intarr AS (arr int[]);
CREATE TYPE
ibox=# SELECT array(
SELECT x::intarr FROM (
SELECT array[2,3]
UNION ALL
SELECT array[3,4]) x(a));
?column?
---
{(\{2,3}\),(\{3,4}\)}
(1 Zeile)
the result seems to be an array with two strings containing escaped
string-represenations of arrays :)
I think that's what you want though--PG just formats the literal the
only way it knows how. You can use the normal array indexing operators
to get the elements out that you want. For example:
SELECT x.arr[1].arr[1]
FROM (SELECT e'{(\\{2,3}\\),(\\{3,4}\\)}'::intarr[]) x(arr);
Or from your original query:
SELECT x.arr[1].arr[1]
FROM (
SELECT array(
SELECT x::intarr FROM (
SELECT array[2,3]
UNION ALL
SELECT array[3,4]) x(a))) x(arr);
If you really do care how the literals are formatted, then you're going
to have to come up with your own data type and associated input and
output functions.
--
Sam http://samason.me.uk/
--
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] creating array of integer[] out of query - how?
On Wed, Sep 2, 2009 at 9:47 AM, Massa, Harald Arminc...@ghum.de wrote:
Sam,
To all: is there a deeper reason why there is no array type for datatype
record available?
Not enough demand :)
seams reasonable :)
Try:
CREATE TYPE intarr AS (arr int[]);
SELECT array(
SELECT x::intarr FROM (
SELECT array[2,3]
UNION ALL
SELECT array[3,4]) x(a));
and it should do the right thing in 8.3.
not exactly :)
ibox=# CREATE TYPE intarr AS (arr int[]);
CREATE TYPE
ibox=# SELECT array(
SELECT x::intarr FROM (
SELECT array[2,3]
UNION ALL
SELECT array[3,4]) x(a));
?column?
---
{(\{2,3}\),(\{3,4}\)}
(1 Zeile)
the result seems to be an array with two strings containing escaped
string-represenations of arrays :)
nope...it's an array of composite types, each type with one field, and
array of two ints. this is waht you wanted? what are you trying to do
exactly?
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] Subselect problem
I'm not sure if I'm making wrong assumptions on the semantics of subselect or if this a problem in PostgreSQL: I'm using query with the following structure select a1.id, c1.foo from a as a1 join b on ... join c as c1 on ... where a.id in ( select a2.id from a as a2 left join c as c2 on ... where c2.bla is null) The query unexpectedly returns an empty result set. When I replace the subselect by the equivalent list of integer literals (i.e. just the ID values returned by the subselect run as a separate query), the result set is no longer empty: select a.id, c.foo from a join b on ... join c on ... where a.id in (123, 456, ... ) My impression is that PostgreSQL somehow confuses the references to the tables a and c from the outer and the inner select. I have tried to isolate the problem with a small set of entries, but when I delete unrelated entries from my tables, the query plan changes and the problem no longer occurs, which again confirms my suspicion that this a problem in Postgres. The problem occurs with PostgreSQL 8.4.0. I cannot reproduce it with PostgreSQL 8.3.7. If this is indeed a bug in Postgres, please let me know the best way to provide more information. The tables in question have a few hundred rows each, so I could probably come up with a script demonstrating the problem. Best regards, Harald *** innovative systems GmbH Navigation-Multimedia Geschaeftsfuehrung: Edwin Summers - Michael Juergen Mauser Sitz der Gesellschaft: Hamburg - Registergericht: Hamburg HRB 59980 *** Diese E-Mail enthaelt vertrauliche und/oder rechtlich geschuetzte Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtuemlich erhalten haben, informieren Sie bitte sofort den Absender und loeschen Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet. This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and delete this e-mail. Any unauthorized copying, disclosure or distribution of the contents in this e-mail is strictly forbidden. *** -- 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] PL/SQL unset custom variable
Gordon Ross gr...@ucs.cam.ac.uk wrote: (I'm using Postgres 8.3) I have a trigger that references a custom variable. Most of the time this custom variable is set, and I have no problems. However, in certain corner cases the custom variable is not set and the trigger fails. Is there a way to either test if the custom variable is set, or to specify a global default for the custom variable ? I think, you can use COALESCE(your_variable, default_value) to solve that problem. Try it, it is untested. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly. (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- 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] Ungooglable error message when running initdb: Symbol not found: _check_encoding_conversion_args
August Lilleaas augustlille...@gmail.com writes: I'm configuring with `./configure --prefix=/usr/local/Cellar/postgresql/8.4.0`, without sudo. I hawe chowned /usr/local so that I don't need to sudo it. I'm getting the following error when running `initdb` after successfully compiling postgresql Symbol not found: _check_encoding_conversion_args Is it possible the 'postgres' you're invoking is not 8.4.0 but something a bit older? That function was added to the backend relatively recently. The other possibility that comes to mind is that this is Snow Leopard-specific breakage. But others have reported successful builds on SL. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] creating array of integer[] out of query - how?
nope...it's an array of composite types, each type with one field, and array of two ints. this is waht you wanted? what are you trying to do exactly? yeah, that is quite what I want, just was surprised by the way psql displayed the result. What I am doing: I have a table: key1 key2 key3 infofield and need/want to transform it into a new dataset with: key1, array([key3, infofield]) where key2=:externvalue using that new type from sam I can do that now. Many thanks! Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - LASIK good, steroids bad?
Re: [GENERAL] how to use multiple schema's
So, can you just set search_path to point to all the schemas at once then? Or does that not meet your needs? On Wed, Sep 2, 2009 at 7:19 AM, Himanshu Guptahimanshu.gu...@semanticbits.com wrote: I want single user to see all the schema at once. Database server is on client side and it takes time to get new user or new database instance, So i am trying to find a workaround for that. Thanks, Himanshu On Sep 1, 2009, at 6:04 PM, Scott Marlowe wrote: On Tue, Sep 1, 2009 at 2:58 PM, Himanshu Guptahimanshu.gu...@semanticbits.com wrote: Hi, I have multiple applications, i want to create separate schema for each of the application. I dont want to change search path, since have only one user. I went through couple of post's talking about some patch related to that. It seems that patch got rejected. Any help is appreciated. I'm not sure how you want this to happen. Do you want a single user to see ALL the schemas at once? Or do you want to set the search path each time you connect? Is there a reason for having multiple apps hit multiple schemas but use only one account? Multiple accounts (one for each app) would certainly make things more manageable. But mainly I'm just trying to get a grip on how you're trying to get this to work. -- When fascism comes to America, it will be intolerance sold as diversity. -- 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] PL/SQL unset custom variable
On 02/09/2009 15:39, Andreas Kretschmer akretsch...@spamfence.net wrote:
Gordon Ross gr...@ucs.cam.ac.uk wrote:
Is there a way to either test if the custom variable is set, or to specify a
global default for the custom variable ?
I think, you can use COALESCE(your_variable, default_value) to solve
that problem. Try it, it is untested.
Sorry, no joy :-(
grails= SELECT current_setting('phone.id');
ERROR: unrecognized configuration parameter phone.id
grails= SELECT coalesce(current_setting('phone.id'),'SYSTEM');
ERROR: unrecognized configuration parameter phone.id
GTG
--
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] Join efficiency
On Wed, Sep 02, 2009 at 10:11:24PM +0900, tanjunhua wrote: thanks for your response. Maybe if you could describe what you want to do in English then the query would make a bit more sense. I just want those records as the below rule: 1. the record of which uid is 2, status is more than 20, bpassword is 0 and realdelflag is 0 in tab_main; 1.1 the record of which kind is 1 in those that filtered through step1; 1.2 the record of which kind is 0 in those that filtered through step1; 1.2.1 the record of which delflag doesn't equal 0 in those filtered through step1.2; 1.2.2 the record of which uid equal 2, printauth equal 2 or 3 and bprtpermit equal 0 in tab_user left join those filtered through step1.2; 1.2.2.1 the record of which mode equal to 0 or 1 in tab_property and left join those filtered through step1.2.2 using id; That's not a very english explanation. That's just a translation of what the code does, and I can do that easily enough myself. What you're missing is what the query means and the intuition as to how to go about understanding what all that really means. I'm guessing there's a clever combination of outer joins that would make this go fast, but I've tried to do the translation but it's all a bit complicated to do in my head. I think it's something like: SELECT COUNT(DISTINCT t1.id) FROM tab_main t1 LEFT JOIN (SELECT TRUE AS userok FROM tab_user WHERE uid = 2 AND printauth IN (2,3) AND bprtpermit = 0 GROUP BY 1) t2 ON TRUE, LEFT JOIN tab_property t3 ON t1.id = t3.id AND t3.mode IN (0,1) WHERE t1.uid = 2 AND t1.status = 21 AND t1.bpassword = 0 AND t1.realdelflag = 0 AND (t1.kind = 1 OR (t1.kind = 0 AND (t1.delflag 0 OR (t2.userok AND t3.id IS NOT NULL; but I'm not sure how much I'd trust that without some testing. It is my first time to use database in practise, could you give me more detail? such as how to decision the WHERE clause complication? how to make the best choice by analyze result? Would you supply some documents about postgresql performance? There are lots of guides around on the internet; google is your friend! Other than trying to rewrite your queries in different ways I'm not sure what to suggest, it'll give you experience which is the important thing. -- Sam http://samason.me.uk/ -- 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] PosgreSQL Service does not Start In Vista
Inigo Barandiaran, 02.09.2009 14:53: Thanks Thomas!. That sounds very interesting. How can I set privileges for writing in data directory for the postgres user account?. Right click on the directory and choose Security. Anything after that is off-topic in this list ;) Or is it very to directly install posgreSQL out of Program Files Directory? Putting application data into Program Files is a very bad idea in general, not only for Postgres. I'm not sure if the current installer still suggests this, but this was a major flaw in the installers I have used before. The installer does offer the possibility to change this, but how many people really take the time to read the wizard pages? Thomas -- 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] PosgreSQL Service does not Start In Vista
Thanks Thomas! Your help is very appreciated :) I'm going to test what you suggest. Thanks again! Thomas Kellerer wrote: Inigo Barandiaran, 02.09.2009 14:53: Thanks Thomas!. That sounds very interesting. How can I set privileges for writing in data directory for the postgres user account?. Right click on the directory and choose Security. Anything after that is off-topic in this list ;) Or is it very to directly install posgreSQL out of Program Files Directory? Putting application data into Program Files is a very bad idea in general, not only for Postgres. I'm not sure if the current installer still suggests this, but this was a major flaw in the installers I have used before. The installer does offer the possibility to change this, but how many people really take the time to read the wizard pages? Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- View this message in context: http://www.nabble.com/PosgreSQL-Service-does-not-Start-In-Vista-tp25255182p25260223.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] Ungooglable error message when running initdb: Symbol not found: _check_encoding_conversion_args
On Wed, Sep 2, 2009 at 4:41 PM, Tom Lane t...@sss.pgh.pa.us wrote: August Lilleaas augustlille...@gmail.com writes: I'm configuring with `./configure --prefix=/usr/local/Cellar/postgresql/8.4.0`, without sudo. I hawe chowned /usr/local so that I don't need to sudo it. I'm getting the following error when running `initdb` after successfully compiling postgresql Symbol not found: _check_encoding_conversion_args Is it possible the 'postgres' you're invoking is not 8.4.0 but something a bit older? That function was added to the backend relatively recently. There is no other postgresql on my system, so that can't be it. The other possibility that comes to mind is that this is Snow Leopard-specific breakage. But others have reported successful builds on SL. Indeed, that's what bothers me. I'll try to use root, create a separate postgres user, and a few other things. I'm sure it's just something trivial I'm overlooking. regards, tom lane -- August Lilleaas
[GENERAL] install postgis in linux server without desktop
I want to know is there any way can install postgis for postgresql so easily, just like one click bin on Ubuntu using command line? I know there is a tool called stack builder which work after version 8.2 for easily installing extensions, I have tried, but it seem doesnt work under command line. I don't know how to install postgis on ubuntu? I go to postgis website and find the install method is complicated, need me to compile three or more things. -- View this message in context: http://www.nabble.com/install-postgis-in-linux-server-without-desktop-tp25258662p25258662.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] how to use multiple schema's
Hi Scott, Thanks for response, bit if I point it to all the schema, I am just wondering how data insertion will work, I have multiple ables with same name in these applications. -HImanshu On Sep 2, 2009, at 10:48 AM, Scott Marlowe wrote: you just set search_path to point to all the schemas at once then? Or does that not meet your needs? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Looking for real configuration data
[Sorry if you receive multiple copies of this message.] [Please feel free to forward the message to others who may be interested.] Hi, We are a computer systems research group at the Computer Science department at Rutgers University, and are conducting research on simplifying the software configuration process. The idea is to leverage the configurations of existing users of a piece of software to ease the configuration process for each new user of the software. The reason for this message is that we would like to collect a large number of deployed configurations to help evaluate our ideas. Thus, we ask systems administrators and end users to submit information about their configurations for any software that they have had to configure, such as Apache, MySQL, and Linux. We hope that you have a few minutes to take our survey which is located at: http://vivo.cs.rutgers.edu/massconf/MassConf.html As an incentive, all surveys completed in their entirety will be entered into a drawing of a number of $50 gift certificates (from Amazon.com). Important: Our work is purely scientific, so we have no interest in any private or commercially sensitive information that may come along with your configuration data. We will make sure that no such information is ever made public. In fact, if you wish, you are more than welcome to anonymize or remove any sensitive information from the configuration data you send us. If you have any questions regarding this message or our work, feel free to email Wei Zheng (wzheng at cs dot rutgers dot edu). Thanks for your time, Wei Zheng PhD student, Vivo Research Group (http://vivo.cs.rutgers.edu) Rutgers University -- 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 use multiple schema's
I want single user to see all the schema at once. Database server is on client side and it takes time to get new user or new database instance, So i am trying to find a workaround for that. Thanks, Himanshu On Sep 1, 2009, at 6:04 PM, Scott Marlowe wrote: On Tue, Sep 1, 2009 at 2:58 PM, Himanshu Guptahimanshu.gu...@semanticbits.com wrote: Hi, I have multiple applications, i want to create separate schema for each of the application. I dont want to change search path, since have only one user. I went through couple of post's talking about some patch related to that. It seems that patch got rejected. Any help is appreciated. I'm not sure how you want this to happen. Do you want a single user to see ALL the schemas at once? Or do you want to set the search path each time you connect? Is there a reason for having multiple apps hit multiple schemas but use only one account? Multiple accounts (one for each app) would certainly make things more manageable. But mainly I'm just trying to get a grip on how you're trying to get this to work. -- 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 Live CD based on CentOS 5.3 and PG 8.4 released
You wanted it, and here we go: I released initial version of my PostgreSQL 8.4 live CD, which is based on CentOS 5.3. It includes the PostgreSQL related packages that I build on http://yum.pgsqlrpms.org , along with PostgreSQL 8.4.0. I'm testing it, works well! https://projects.centos.org/trac/livecd/ Please let me know if you have any questions regarding this live CD. I ussually use pglive cd of Bernier, but now seems to be not updated. But have some nice features for example: - has a direct link for psql console in the bar - same for pgadmin - has several docs in html format, in a /var/www and when the livecd starts, firefox displays the links for each doc (all the docs are in the cd, thats is the better) - has a postgres wall paper Could be great add-ons, what do you think about it? if i can help let me know. Regards! -- Emanuel Calvo Franco DBA at: www.siu.edu.ar www.emanuelcalvofranco.com.ar -- 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] install postgis in linux server without desktop
On 09/02/2009 08:20 PM, shane_china wrote: I want to know is there any way can install postgis for postgresql so easily, just like one click bin on Ubuntu using command line? I know there is a tool called stack builder which work after version 8.2 for easily installing extensions, I have tried, but it seem doesnt work under command line. I don't know how to install postgis on ubuntu? I go to postgis website and find the install method is complicated, need me to compile three or more things. use the stackbuilder to get hold of the postgis installer binary. (It gets downloaded in /tmp by default). Then use the binary with --mode text as an option. Check --help for all options available. -- Regards, Sachin Srivastava www.enterprisedb.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] install postgis in linux server without desktop
On Wed, 2009-09-02 at 22:23 +0530, Sachin Srivastava wrote: On 09/02/2009 08:20 PM, shane_china wrote: I want to know is there any way can install postgis for postgresql so easily, just like one click bin on Ubuntu using command line? sudo apt-get install postgresql-8.3-postgis Then there will likely be something to the affect of: psql -U user db /usr/share/postgresql/8.3/contrib/postgis.sql (or something like that) Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering -- 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 use multiple schema's
OOPS I got caught by the reply verses reply-all and only sent this to Scott. Plus I see it has already been addressed in a later message. raa Scott Marlowe wrote: So, can you just set search_path to point to all the schemas at once then? Or does that not meet your needs? The only issue I see is if two or more of the applications have a table named the same, like users. Which gets used? The first found in the search_path? \\||/ Rod -- On Wed, Sep 2, 2009 at 7:19 AM, Himanshu Guptahimanshu.gu...@semanticbits.com wrote: I want single user to see all the schema at once. Database server is on client side and it takes time to get new user or new database instance, So i am trying to find a workaround for that. Thanks, Himanshu On Sep 1, 2009, at 6:04 PM, Scott Marlowe wrote: On Tue, Sep 1, 2009 at 2:58 PM, Himanshu Guptahimanshu.gu...@semanticbits.com wrote: Hi, I have multiple applications, i want to create separate schema for each of the application. I dont want to change search path, since have only one user. I went through couple of post's talking about some patch related to that. It seems that patch got rejected. Any help is appreciated. I'm not sure how you want this to happen. Do you want a single user to see ALL the schemas at once? Or do you want to set the search path each time you connect? Is there a reason for having multiple apps hit multiple schemas but use only one account? Multiple accounts (one for each app) would certainly make things more manageable. But mainly I'm just trying to get a grip on how you're trying to get this to work. -- 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 use multiple schema's
On Wed, Sep 02, 2009 at 10:52:07AM -0400, Himanshu Gupta wrote: Thanks for response, bit if I point it to all the schema, I am just wondering how data insertion will work, I have multiple ables with same name in these applications. Do you know that you can schema qualify table names don't you? For example, if you have a users table in schemas app1 and app2, you can refer to the table in the first schema as: SELECT uid, name FROM app1.users; -- Sam http://samason.me.uk/ -- 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 Live CD based on CentOS 5.3 and PG 8.4 released
Thanks for this! It's a great resource for the community. 2009/9/2 Devrim GÜNDÜZ dev...@gunduz.org: You wanted it, and here we go: I released initial version of my PostgreSQL 8.4 live CD, which is based on CentOS 5.3. It includes the PostgreSQL related packages that I build on http://yum.pgsqlrpms.org , along with PostgreSQL 8.4.0. Details are here: http://yum.pgsqlrpms.org/livecd.php http://www.pglivecd.org This live CD has current versions of many software, like pgAdmin III, phpPgAdmin, Apache, PHP, GNOME, Pidgin, Firefox etc. Kickstart file is configurable, so you can also create your own PostgreSQL Live CD's fairly easily, if you have a CentOS 5.3 machine around, as described in here: https://projects.centos.org/trac/livecd/ Please let me know if you have any questions regarding this live CD. Sincerely, -- Devrim GÜNDÜZ, RHCE Command Prompt - http://www.CommandPrompt.com devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org -- When fascism comes to America, it will be intolerance sold as diversity. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] [Q] optmizing postgres for 'single client' / many small queries
Hi, our application is using Postgres in a rather unusuall way. It is used by a GUI application to store several hundred thousand 'parameters'. Basically it is used like a big INI file. There are about 50 tables with various parameters. The application typicall goes like this select id, child_tb_key_id, fields with parms from tb1 then for each selected row above select from the child table do a select (like the above) and so on -- many levels deep I know that it is not a proper way to use SQL Instead we should be selecting many rows at once, joining them/etc But it is what it is now... Queries are very fast though, Postgres reports that the all the queries for a typical 'load' operation take 0.8 seconds -- however overall time that the GUI user perceives is 8 seconds. Out of that 8 seconds a big chunk is in the sending of the SQL statements/receiving results back -- just network traffic, parsing/etc There are total about 2400 queries that happen in that period of time (just selects) I am trying to figure out how can I optimize PG configuration to suite such a contrived deployment of Postgres. For example, we do not mind PG running on the same machine as the Client app (it is connected via Qt Sql Pg plugin (so it uses Pg native access library underneath). Are there any optmization can be done for that? Also this is a 'single' client/single connection system what optimizations can be done for that? and finally since most of the queries are very quick index-based selects what can be done to optimize the traffic between pg and the client? thank you in advance for any recommendations/pointers. -- Vlad P author of C++ ORM http://github.com/vladp/CppOrm/tree/master -- http://www.fastmail.fm - Send your email first class -- 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 use multiple schema's
Sam Mason wrote: On Wed, Sep 02, 2009 at 10:52:07AM -0400, Himanshu Gupta wrote: Thanks for response, bit if I point it to all the schema, I am just wondering how data insertion will work, I have multiple ables with same name in these applications. Do you know that you can schema qualify table names don't you? For example, if you have a users table in schemas app1 and app2, you can refer to the table in the first schema as: SELECT uid, name FROM app1.users; Himanshu didn't mention if he has access to the code for the applications or can edit them. \\||/ Rod -- -- 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] [Q] optmizing postgres for 'single client' / many small queries
Hello Are you sure, so you have to use PostgreSQL - maybe SQLite or memcached is better for your task. regards Pavel Stehule 2009/9/2 V S P torea...@fastmail.fm: Hi, our application is using Postgres in a rather unusuall way. It is used by a GUI application to store several hundred thousand 'parameters'. Basically it is used like a big INI file. There are about 50 tables with various parameters. The application typicall goes like this select id, child_tb_key_id, fields with parms from tb1 then for each selected row above select from the child table do a select (like the above) and so on -- many levels deep I know that it is not a proper way to use SQL Instead we should be selecting many rows at once, joining them/etc But it is what it is now... Queries are very fast though, Postgres reports that the all the queries for a typical 'load' operation take 0.8 seconds -- however overall time that the GUI user perceives is 8 seconds. Out of that 8 seconds a big chunk is in the sending of the SQL statements/receiving results back -- just network traffic, parsing/etc There are total about 2400 queries that happen in that period of time (just selects) I am trying to figure out how can I optimize PG configuration to suite such a contrived deployment of Postgres. For example, we do not mind PG running on the same machine as the Client app (it is connected via Qt Sql Pg plugin (so it uses Pg native access library underneath). Are there any optmization can be done for that? Also this is a 'single' client/single connection system what optimizations can be done for that? and finally since most of the queries are very quick index-based selects what can be done to optimize the traffic between pg and the client? thank you in advance for any recommendations/pointers. -- Vlad P author of C++ ORM http://github.com/vladp/CppOrm/tree/master -- http://www.fastmail.fm - Send your email first class -- 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] PostgreSQL Live CD based on CentOS 5.3 and PG 8.4 released
Hi, On Wed, 2009-09-02 at 13:47 -0300, Emanuel Calvo Franco wrote: I released initial version of my PostgreSQL 8.4 live CD, which i based on CentOS 5.3. It includes the PostgreSQL related packages that I build on http://yum.pgsqlrpms.org , along with PostgreSQL 8.4.0. I'm testing it, works well! Thanks for testing. I ussually use pglive cd of Bernier, but now seems to be not updated. But have some nice features for example: - has a direct link for psql console in the bar - same for pgadmin I added these features to the kickstart file. They will appear in next build, which will happen next Monday with PostgreSQL 8.4.1 . - has several docs in html format, in a /var/www and when the livecd starts, firefox displays the links for each doc (all the docs are in the cd, thats is the better) - has a postgres wall paper I need to think about these -- and if they are added, they will appear in next set, too. Thanks for valuable comments. Regards, -- Devrim GÜNDÜZ, RHCE Command Prompt - http://www.CommandPrompt.com devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org signature.asc Description: This is a digitally signed message part
Re: [GENERAL] [Q] optmizing postgres for 'single client' / many small queries
Hi, yes, I am sure I have to continue supporting Postgres at this time, it would take enormous effor to change to something else But yes, sqlite or tokiocabinet in my view would be good options (the decison was made some time ago, unfortunately). On Wed, 02 Sep 2009 19:49 +0200, Pavel Stehule pavel.steh...@gmail.com wrote: Hello Are you sure, so you have to use PostgreSQL - maybe SQLite or memcached is better for your task. regards Pavel Stehule 2009/9/2 V S P torea...@fastmail.fm: Hi, our application is using Postgres in a rather unusuall way. It is used by a GUI application to store several hundred thousand 'parameters'. Basically it is used like a big INI file. There are about 50 tables with various parameters. The application typicall goes like this select id, child_tb_key_id, fields with parms from tb1 then for each selected row above select from the child table do a select (like the above) and so on -- many levels deep I know that it is not a proper way to use SQL Instead we should be selecting many rows at once, joining them/etc But it is what it is now... Queries are very fast though, Postgres reports that the all the queries for a typical 'load' operation take 0.8 seconds -- however overall time that the GUI user perceives is 8 seconds. Out of that 8 seconds a big chunk is in the sending of the SQL statements/receiving results back -- just network traffic, parsing/etc There are total about 2400 queries that happen in that period of time (just selects) I am trying to figure out how can I optimize PG configuration to suite such a contrived deployment of Postgres. For example, we do not mind PG running on the same machine as the Client app (it is connected via Qt Sql Pg plugin (so it uses Pg native access library underneath). Are there any optmization can be done for that? Also this is a 'single' client/single connection system what optimizations can be done for that? and finally since most of the queries are very quick index-based selects what can be done to optimize the traffic between pg and the client? thank you in advance for any recommendations/pointers. -- Vlad P author of C++ ORM http://github.com/vladp/CppOrm/tree/master -- http://www.fastmail.fm - Send your email first class -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Vlad P author of C++ ORM http://github.com/vladp/CppOrm/tree/master -- http://www.fastmail.fm - Accessible with your email software or over the web -- 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] Subselect problem
Wellmann, Harald harald.wellm...@harman.com writes: The problem occurs with PostgreSQL 8.4.0. I cannot reproduce it with PostgreSQL 8.3.7. There are known bugs in 8.4.0 having to do with improperly exchanging the ordering of semijoins (IN joins) and other joins. You haven't provided enough information to test whether your case is one of them. If you can try CVS branch tip or a recent nightly snapshot, there might still be enough time to do something about it for 8.4.1, if it isn't fixed already. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PL/SQL unset custom variable
Gordon Ross gr...@ucs.cam.ac.uk writes:
Sorry, no joy :-(
grails= SELECT current_setting('phone.id');
ERROR: unrecognized configuration parameter phone.id
grails= SELECT coalesce(current_setting('phone.id'),'SYSTEM');
ERROR: unrecognized configuration parameter phone.id
You could use a SELECT from pg_settings to probe for the value
of the parameter without getting an error.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [Q] optmizing postgres for 'single client' / many small queries
V S P torea...@fastmail.fm writes: The application typicall goes like this select id, child_tb_key_id, fields with parms from tb1 then for each selected row above select from the child table do a select (like the above) and so on -- many levels deep Seems like you need to fix your data representation so that this operation can be collapsed into one query. The main problem looks to be a bogus decision to have separate child tables rather than one big table with an extra key column. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [Q] optmizing postgres for 'single client' / many small queries
Well, actually
somebody has written a C++ ORM
that allows to do things (just a sketch)
class L: CDbCapable
{
public:
int prop1;
int prop2;
}
class A: CDbCapable
{
QArrayL list_of_props_xyz;
}
int main ()
{
A inst1;
inst1.create_or_update_DbSchemaIfNeeded();
inst1.readFromDb();
//modifying something
inst1.writeToDb();
}
As it is well known C++ lack of Reflection prevents it from having
standardise Data serialization
libraries to files or to Databases.
So in-house a mechanism was developed to do the above. It took some
time and it is not possible to just yank it out.
Of course, internally in the ORM's implementation a somewhat
questionable decision was made that
to process arrays of 'children' for a given instance would require
separate SQL statements.
That's where the problem comes from, I understand what needs to be done
to redesign the approach/etc. And that
will take more time than currently is available.
Therefore, I just wanted to ask if there there are some things in Pg
that can I can experiment with
(local client/server communcations via IPC, reducing the speed of SQL
parses, any other possible tricks)
Thank you in advance
On Wed, 02 Sep 2009 14:26 -0400, Tom Lane t...@sss.pgh.pa.us wrote:
V S P torea...@fastmail.fm writes:
The application typicall goes like this
select id, child_tb_key_id, fields with parms from tb1
then for each selected row above
select from the child table do a select (like the above)
and so on -- many levels deep
Seems like you need to fix your data representation so that this
operation can be collapsed into one query. The main problem looks
to be a bogus decision to have separate child tables rather than
one big table with an extra key column.
regards, tom lane
--
Vlad P
author of C++ ORM http://github.com/vladp/CppOrm/tree/master
--
http://www.fastmail.fm - mmm... Fastmail...
--
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] [Q] optmizing postgres for 'single client' / many small queries
V S P torea...@fastmail.fm writes: Well, actually somebody has written a C++ ORM [ that is causing all your problems and you say you can't discard ] Just out of curiosity, does anyone know of any ORM anywhere that doesn't suck? They seem to be uniformly awful, at least in terms of their interfaces to SQL databases. If there were some we could recommend, maybe people would be less stuck with these bogus legacy architectures. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [Q] optmizing postgres for 'single client' / many small queries
I do not know of any for C++. That's why I started my own (which is not the one used for the problem I am having :- ) ) http://github.com/vladp/CppOrm it works with Pg 8.3+ and VC++ compiler sofar (but support for more platforms and Dbs will be added in the future). My Orm is not really an ORM because I did not implement anything that would 'traverse' object instance relationships (which is what the ORM in question here is doing). Instead I just automagically generate SQL code for insert/update/deletes for classes that map to tables (one-to-one). The basic problem is that C++ standards comittee in my view just sucks... i do not have any better words for it. It is because of lack of reflection (ability to identify at runtime variable names/functions names) that an ORM, or HTTP session storage/retrival mechanism, JSON/XML parsers that parse text right into class instances -- cannot be implemented Basically the things that are needed to deal with 'Typeless' data at runtime (such that XML/JSON/Database queries) and map that data to the C++ object instances. Which is in the 'high-level view' why C++ is not used for web development. Yes there are 'attempts' in that area -- but all are different, require quite a bit of sophistication and are not complete (The reflection mechanism I implemented for my cpporm is not complete either). If C++ would have supported Reflection -- the there would be C++_Hibernate, C++_LINQ, C++_json, C++_xml, C++_HTTP, C++_HTTPSession and so on... (and no they would have been memory hogs -- thanks to now standard reference counting in C++ via shared_ptr and good use of allocators) sorry for the rant, still looking for any bright ideas on optimizing for many small queries/local db host situations. Thanks On Wed, 02 Sep 2009 14:45 -0400, Tom Lane t...@sss.pgh.pa.us wrote: V S P torea...@fastmail.fm writes: Well, actually somebody has written a C++ ORM [ that is causing all your problems and you say you can't discard ] Just out of curiosity, does anyone know of any ORM anywhere that doesn't suck? They seem to be uniformly awful, at least in terms of their interfaces to SQL databases. If there were some we could recommend, maybe people would be less stuck with these bogus legacy architectures. regards, tom lane -- Vlad P author of C++ ORM http://github.com/vladp/CppOrm/tree/master -- http://www.fastmail.fm - Faster than the air-speed velocity of an unladen european swallow -- 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] PL/SQL unset custom variable
Hmmm.
If I do:
select * from pg_settings where name='custom_variable_classes';
I see my entry phone, but I can't see how I can tell if I've set phone.id
GTG
From: Tom Lane [...@sss.pgh.pa.us]
Sent: 02 September 2009 19:16
To: Gordon Ross
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] PL/SQL unset custom variable
Gordon Ross gr...@ucs.cam.ac.uk writes:
Sorry, no joy :-(
grails= SELECT current_setting('phone.id');
ERROR: unrecognized configuration parameter phone.id
grails= SELECT coalesce(current_setting('phone.id'),'SYSTEM');
ERROR: unrecognized configuration parameter phone.id
You could use a SELECT from pg_settings to probe for the value
of the parameter without getting an error.
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] binary format of array stored as text
I have an old table with a column specified as integer[]. The data within this
column was stored as text ala {1,2,3,...} yada yada. I have an application
where I am reading all the columns from a row in this table in binary format.
Does anybody know how I can recast/reshape this binary data back into an array
of integers?
The array in text format is:
{0,3,3,18,19,19,35,35}
The array in binary format is:
0001 0017 0008
0001 0004 0004
0003 0004 0003 0004
0012 0004 0013 0004
0013 0004 0023 0004
0023
I can make some assumptions to interpet 0004 0023 as a 32-bit
length followed by length bytes (length = 4, data = 0x23 = 35), but I have no
clue how to interpret the leading 20 bytes of this data.
I appreciate any help I can get.
Thanks,
Dave Huber
This electronic mail message is intended exclusively for the individual(s) or
entity to which it is addressed. This message, together with any attachment, is
confidential and may contain privileged information. Any unauthorized review,
use, printing, retaining, copying, disclosure or distribution is strictly
prohibited. If you have received this message in error, please immediately
advise the sender by reply email message to the sender and delete all copies of
this message.
THIS E-MAIL IS NOT AN OFFER OR ACCEPTANCE: Notwithstanding the Uniform
Electronic Transactions Act or any other law of similar import, absent an
express statement to the contrary contained in this e-mail, neither this e-mail
nor any attachments are an offer or acceptance to enter into a contract, and
are not intended to bind the sender, LeTourneau Technologies, Inc., or any of
its subsidiaries, affiliates, or any other person or entity.
WARNING: Although the company has taken reasonable precautions to ensure no
viruses are present in this email, the company cannot accept responsibility for
any loss or damage arising from the use of this email or attachments.
[GENERAL] Should I create an index for partition with fixed key?
Hello. Some days ago I started using partitioning to make it possible to work with large amount of data. I created master table as documentation suggest, created some child tables which inherit from the master one and then added a constraint for each child table to define the allowed key value. Constraint was like: CHECK (site_id = 'google_com') Now here is my question: While reading the documentation I came across the following suggestion: For each partition, create an index on the key column(s) Should I do that if the key will be the same for the whole partition? Will there be any benefits of having an index on site_id column? When I first read it, I decided that if all rows have the same key (say site_id = 'google_com') then there is no need in index. Thanks. -- Sergey Samokhin -- 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] binary format of array stored as text
On Wed, Sep 2, 2009 at 3:50 PM, Dave
Huberdhu...@letourneautechnologies.com wrote:
I have an old table with a column specified as integer[]. The data within
this column was stored as text ala {1,2,3,…} yada yada. I have an
application where I am reading all the columns from a row in this table in
binary format. Does anybody know how I can recast/reshape this binary data
back into an array of integers?
check out our libpqtypes library:
http://libpqtypes.esilo.com/
It pulls all data as binary and allows you to access array elements,
and you can write data the same way. PostgreSQL binary formats are
more complicated than you think: there is dimension information, etc.
in there. (that said, sending int[] as binary is much faster).
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] Ungooglable error message when running initdb: Symbol not found: _check_encoding_conversion_args
- On Wed, 9/2/09, August Lilleaas augustlille...@gmail.com wrote: From: August Lilleaas augustlille...@gmail.com Subject: [GENERAL] Ungooglable error message when running initdb: Symbol not found: _check_encoding_conversion_args To: pgsql-general@postgresql.org Date: Wednesday, September 2, 2009, 7:52 AM Hello there, I'm configuring with `./configure --prefix=/usr/local/Cellar/postgresql/8.4.0`, without sudo. I hawe chowned /usr/local so that I don't need to sudo it. I'm getting the following error when running `initdb` after successfully compiling postgresql Symbol not found: _check_encoding_conversion_args Here's the full output. augu...@honk:~$ initdb -D /usr/local/Cellar/postgresql/8.4.0/defaultdb The files belonging to this database system will be owned by user augustl. This user must also own the server process. The database cluster will be initialized with locales COLLATE: C CTYPE: UTF-8 MESSAGES: C MONETARY: C NUMERIC: C TIME: C The default database encoding has accordingly been set to UTF8. initdb: could not find suitable text search configuration for locale UTF-8 The default text search configuration will be set to simple. creating directory /usr/local/Cellar/postgresql/8.4.0/defaultdb ... ok creating subdirectories ... ok selecting default max_connections ... 20 selecting default shared_buffers ... 2400kB creating configuration files ... ok creating template1 database in /usr/local/Cellar/postgresql/8.4.0/defaultdb/base/1 ... ok initializing pg_authid ... ok initializing dependencies ... ok creating system views ... ok loading system objects' descriptions ... ok creating conversions ... FATAL: could not load library /usr/local/Cellar/postgresql/8.4.0/lib/ascii_and_mic.so: dlopen(/usr/local/Cellar/postgresql/8.4.0/lib/ascii_and_mic.so, 10): Symbol not found: _check_encoding_conversion_args Referenced from: /usr/local/Cellar/postgresql/8.4.0/lib/ascii_and_mic.so Expected in: /usr/local/Cellar/postgresql/8.4.0/bin/postgres in /usr/local/Cellar/postgresql/8.4.0/lib/ascii_and_mic.so STATEMENT: CREATE OR REPLACE FUNCTION ascii_to_mic (INTEGER, INTEGER, CSTRING, INTERNAL, INTEGER) RETURNS VOID AS '$libdir/ascii_and_mic', 'ascii_to_mic' LANGUAGE C STRICT; child process exited with exit code 1 initdb: removing data directory /usr/local/Cellar/postgresql/8.4.0/defaultdb augu...@honk:~$ I found something on google about the file ascii_and_mic.so not existing, but that's not the case here; the file does indeed exist. Googling _check_encoding_conversion_args doesn't yield any results. My system is OS X 10.6 (Snow Leopard). I'm working with http://ftp9.us.postgresql.org/pub/mirrors/postgresql/source/v8.4.0/postgresql-8.4.0.tar.gz. i dont know the OS x 10 system but you have checked the permissions of ascii_and_mic.so, this file must have the owner and group whit the user creating the cluster
[GENERAL] problems with function pg_catalog.btrim(date)
Hi everybody, I hope some one can help me, I have created the following function but I always get the error that there is not function pg_catalog.btrim(date), in the version postgresql 8.1 it worked but now it does not so I don't know if it is related with a configuration problem of the postgresql or somethin in the function code : thanks karina CREATE FUNCTION edad(date) RETURNS integer AS $_$ DECLARE fechnac ALIAS FOR $1; dia_nac integer; mes_nac integer; anho_nac integer; fecha_nac date; fecha_temp text; hoy date; dia integer; mes integer; anho integer; temp integer; edad integer; BEGIN hoy := current_date; hoy := date(trim(hoy)); fecha_nac:= date(trim(fechnac)); fecha_temp:=CAST(fecha_nac AS text ); dia_nac :=CAST(substring(fecha_temp from 1 for 2) AS integer ); mes_nac := CAST (substring(fecha_temp from 4 for 2) AS integer); anho_nac :=CAST(substring(fecha_temp from 7 for 4) AS integer ); dia :=CAST(substring(hoy from 1 for 2) AS integer ); mes := CAST (substring(hoy from 4 for 2) AS integer); anho := CAST(substring(hoy from 7 for 4) AS integer ); edad:= (anho-anho_nac)-1; IF mes+1-mes_nac 0 THEN RETURN edad; END IF; IF mes+1-mes_nac 0 THEN RETURN edad; END IF; IF mes+1-mes_nac 0 THEN RETURN edad+1; END IF; IF dia-dia_nac = 0 THEN RETURN edad+1; ELSE RETURN edad; END IF; END; $_$ LANGUAGE plpgsql; -- 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] [Q] optmizing postgres for 'single client' / many small queries
On Wed, Sep 02, 2009 at 02:45:39PM -0400, Tom Lane wrote: V S P torea...@fastmail.fm writes: Well, actually somebody has written a C++ ORM [ that is causing all your problems and you say you can't discard ] Just out of curiosity, does anyone know of any ORM anywhere that doesn't suck? They seem to be uniformly awful, at least in terms of their interfaces to SQL databases. If there were some we could recommend, maybe people would be less stuck with these bogus legacy architectures. Hibernate has the very nice feature of being able to get out of your way. Properly used, it can keep completely out of the business of making (wrong) guesses based on DDL, which is what ORMs often do. DBIx::Class http://search.cpan.org/dist/DBIx-Class/ has gone a long way in the right direction. Ones which (attempt to) dictate decisions about DDL are just off the map. :P Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] install postgis in linux server without desktop
I do follow your instruction. I successfully install postgis, but I can't find postgis.sql under any folder. My postgresql installed by apt-get. After apt-get postgis, What should I to do? Only execute sql in postgis.sql? Joshua D. Drake wrote: On Wed, 2009-09-02 at 22:23 +0530, Sachin Srivastava wrote: On 09/02/2009 08:20 PM, shane_china wrote: I want to know is there any way can install postgis for postgresql so easily, just like one click bin on Ubuntu using command line? sudo apt-get install postgresql-8.3-postgis Then there will likely be something to the affect of: psql -U user db /usr/share/postgresql/8.3/contrib/postgis.sql (or something like that) Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- View this message in context: http://www.nabble.com/install-postgis-in-linux-server-without-desktop-tp25258662p25267637.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] install postgis in linux server without desktop
How to use the stackbuilder to get hold of the postgis installer binary, could you explain it more exactly? I execute stackbuilder, but there is an error r...@ubuntu-yingxia:/opt/PostgreSQL/8.4/stackbuilder/bin# ./stackbuilder ./stackbuilder: error while loading shared libraries: libgtk-x11-2.0.so.0: cannot open shared object file: No such file or directory thank you for help Sachin Srivastava-2 wrote: On 09/02/2009 08:20 PM, shane_china wrote: I want to know is there any way can install postgis for postgresql so easily, just like one click bin on Ubuntu using command line? I know there is a tool called stack builder which work after version 8.2 for easily installing extensions, I have tried, but it seem doesnt work under command line. I don't know how to install postgis on ubuntu? I go to postgis website and find the install method is complicated, need me to compile three or more things. use the stackbuilder to get hold of the postgis installer binary. (It gets downloaded in /tmp by default). Then use the binary with --mode text as an option. Check --help for all options available. -- Regards, Sachin Srivastava www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- View this message in context: http://www.nabble.com/install-postgis-in-linux-server-without-desktop-tp25258662p25267681.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] problems with function pg_catalog.btrim(date)
Karina Guardado karina.guard...@ues.edu.sv writes: I hope some one can help me, I have created the following function but I always get the error that there is not function pg_catalog.btrim(date), in the version postgresql 8.1 it worked but now it does not so I don't know if it is related with a configuration problem of the postgresql or somethin in the function code : What in the world do you imagine that trim() on a date would be good for? Just get rid of the date(trim()) lines. I think this accidentally failed to fail pre-8.3 because there was an implicit cast from date to text, so it would convert the date to text, remove leading/trailing blanks (which there wouldn't be any of), and then convert the string back to date. An expensive and pointless no-op. This bit is going to fail too: dia :=CAST(substring(hoy from 1 for 2) AS integer ); nmes := CAST (substring(hoy from 4 for 2) AS integer); anho := CAST(substring(hoy from 7 for 4) AS integer ); While that sort of worked in 8.1, it was always bad coding style and fragile as can be --- think what will happen if the user changes the datestyle setting. Replace this with extract(day ... ) and so forth, and you'll have code that is safer, faster, and more standard/portable. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Add a serial column to a table based on a sort clause
On Wed, Sep 02, 2009 at 04:40:13PM +0400, Igor Katson wrote: I have a table, which has a creation_ts (timestamp) column, but does not have a id (serial) column. I want to add such a one, but, AFAIK, if I enter ALTER TABLE table ADD COLUMN id serial it will randomly put the sequence numbers. Random is how you should think of them. Sequences guarantee only uniqueness. Neither order nor gap-less numbers, nor any other property apply to them. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] print/return only the first X chars of a varchar column?
Hi,
I think you can use the overlay function to do this.
http://www.postgresql.org/docs/current/static/functions-string.html
create table t1 (f char(10));
insert into t1 values ('abcdefg'),('hijklmno');
-- the below cmd will display only the first 2 characters of f.
-- if you want trim the spaces :) after this.
select overlay(f placing ' ' from 3 to 10);
Regards
kalyan
***
This e-mail and attachments contain confidential information from HUAWEI,
which is intended only for the person or entity whose address is listed
above. Any use of the information contained herein in any way (including,
but not limited to, total or partial disclosure, reproduction, or
dissemination) by persons other than the intended recipient's) is
prohibited. If you receive this e-mail in error, please notify the sender by
phone or email immediately and delete it!
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Kevin Kempter
Sent: Monday, August 31, 2009 8:19 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] print/return only the first X chars of a varchar column?
Hi all;
I'm selecting from a table that has a varchar(1000) but I only want to
display
the firs 20 characters. Looked at the string functions in the docs but
nothing
jumped out...
Suggestions?
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] Audit Trigger puzzler
Most of the time, my application will set the edited_by field to
reflect an application username (i.e., the application logs into the
database as a database user, and that's not going to be the
application user) So I log into my application as Dave, but the
application connects to the database as dbuser.
If the app doesn't specifically send an edited_by value in it's
update, then I want to default that value to the database user.
This would also be good for auditing any manual data changes that
could happen at the psql level.
In Oracle, the way we handle audit triggers is by using Package
Variables. We emulate some of that functionality in postgresql by
adding a custom variable to the configuration file:
custom_variable_classes = 'mysess'
Then, whenever a user logs into the application, my login procedure
calls this function:
CREATE OR REPLACE FUNCTION begin_sess(staffid character varying)
RETURNS void AS $BODY$ BEGIN
PERFORM set_config('mysess.curr_user', coalesce(staffid,''), false);
END; $BODY$ LANGUAGE 'plpgsql' VOLATILE;
This makes the current application user automatically available to every
function, including triggers. Then, in your triggers, you can do
this:
DECLARE
curr_user staff.staff_id%TYPE;
BEGIN
SELECT current_setting('mysess.curr_user') INTO curr_user;
In your trigger, you could check that this variable was unset, and fall
back to the database user.
HTH.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] handle audiofiles in postgres
Can anyone used or tell me how to handle audio files in postgres Audio files may be in wav / vox / dss format and each have average 30 min running time. Thanks in advance Regards edi
Re: [GENERAL] handle audiofiles in postgres
edisan wrote: Can anyone used or tell me how to handle audio files in postgres Audio files may be in wav / vox / dss format and each have average 30 min running time. probably too large to want to store in the database, as a 30 minute PCM .WAV file in CD quality could easily be 300 megabytes, even in a MP3 format it might be 30MB if high-fidelity, and maybe 6MB if lower voice quality, thats -still- larger than I'd want to store as database rows, anyways, there's nothing really relational about the audio data. I'd suggest storing them as standard disk files, and putting the location (path, filename) in the database.if you need to access them from multiple systems, store them on an http or similar server -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
