Re: [GENERAL] looking for psql without server-installation
On 2008-08-06 17:38, [EMAIL PROTECTED] wrote: what is the best way to get a psql-client at a linux system without the need of a server. On Fedora/RedHat/CentOS or other yum based distribution: # yum install postgresql For Debian/Ubuntu or other dpkg based distribution I think it will be: # dpkg --install postgresql-client But I do not use Debian, so I can be wrong. It does not have to be the same version as the server, so don't compile it yourself. Regards Tometzky -- ...although Eating Honey was a very good thing to do, there was a moment just before you began to eat it which was better than when you were... Winnie the Pooh -- 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] bytea encode performance issues
I ran the update, but now (obviously) it wants to vacuum again and vacuum on that table took 9 hours yesterday. Do the statistics change when changing the storage type? Meaning does it really need to vacuum? Thank you Sim Tom Lane wrote: Sim Zacks [EMAIL PROTECTED] writes: After the alter table, then I have to update each row with an update dbmail_messageblks set messageblk=messageblk; so that it uses the new storage. I wouldn't actually bet on that changing anything at all ... I'd try something like messageblk = messageblk || '' to make completely sure the value gets detoasted. 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] Checkpoints writes
Hi guys, What's the way to count the read/write bytes of the checkpoint process before 8.3 (no pg_stat_bgwriter view :-[ ) I want to distinguish bytes written by checkpoints and others written by the background process thanks -- Cyril SCETBON -- 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] looking for psql without server-installation
maybe i misworded my problem. i am looking for the possibility to install a psql client (without a server) out of the sources. my usecase is: one server with a postgresql-server (self compiled) and several workstations with psql-clients, also preferred self compiled. i know that is possible to compile at every workstation the sourcecode of the server to get a psql-client. but the side effect is, that in this case i have a potential server at every workstation and in my opinion that is a very large footprint. i have to compile psql for myself because otherwise (installing binaries with packaging tools) i have no (or minimal) control about the version of the client. i think that is a use case which can be applied to the needs of many people (for example: oracle offers also client-only-packages which are containing sql+, ...) hoping for a solution christian -Ursprüngliche Nachricht- Von: Tomasz Ostrowski [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 7. August 2008 08:47 An: Strobl, Christian Cc: pgsql-general@postgresql.org Betreff: Re: looking for psql without server-installation On 2008-08-06 17:38, [EMAIL PROTECTED] wrote: what is the best way to get a psql-client at a linux system without the need of a server. On Fedora/RedHat/CentOS or other yum based distribution: # yum install postgresql For Debian/Ubuntu or other dpkg based distribution I think it will be: # dpkg --install postgresql-client But I do not use Debian, so I can be wrong. It does not have to be the same version as the server, so don't compile it yourself. Regards Tometzky -- ...although Eating Honey was a very good thing to do, there was a moment just before you began to eat it which was better than when you were... Winnie the Pooh -- 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] looking for psql without server-installation
[EMAIL PROTECTED] wrote: maybe i misworded my problem. i am looking for the possibility to install a psql client (without a server) out of the sources. my usecase is: one server with a postgresql-server (self compiled) and several workstations with psql-clients, also preferred self compiled. i know that is possible to compile at every workstation the sourcecode of the server to get a psql-client. but the side effect is, that in this case i have a potential server at every workstation and in my opinion that is a very large footprint. i have to compile psql for myself because otherwise (installing binaries with packaging tools) i have no (or minimal) control about the version of the client. i think that is a use case which can be applied to the needs of many people (for example: oracle offers also client-only-packages which are containing sql+, ...) hoping for a solution Just run the make and make install commands in subdirs only. You will need to do it in src/interfaces/libpq and src/bin/psql to get a working psql. But you can certainly have control over the version on the client even if you use binary packages like .RPMs. Just decide which version you install. The OS may come with a pre-selected version by default, but for most OSes there are RPMs or DEBs available for other versions as well that you can install. //Magnus -- 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] looking for psql without server-installation
On 2008-08-07 11:49, [EMAIL PROTECTED] wrote: maybe i misworded my problem. i am looking for the possibility to install a psql client (without a server) out of the sources. It is not a basic problem - it is your solution for a problem, which maybe does have another, better solution. i have to compile psql for myself because otherwise (installing binaries with packaging tools) i have no (or minimal) control about the version of the client. You can use official yum repository and you'll have total control of client version. It works for RedHatEL/CentOS/Fedora: http://yum.pgsqlrpms.org/reporpms/repoview/ Regards Tometzky -- ...although Eating Honey was a very good thing to do, there was a moment just before you began to eat it which was better than when you were... Winnie the Pooh -- 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] looking for psql without server-installation
hi tomasz, thanks for your answers. unfortunately i misworded my problem again. i have a very clear problem and i am looking for a solution of this problem (if it is basic or not) and i am not using RedHatEL/CentOS/Fedora. i am looking for a solution which is applicable to every linux os and not only for some. so here again my problem: i am looking for the possibility to install a psql client (without a server) FROM THE SOURCE. my usecase is: one server with a postgresql-server (self compiled) and several workstations with psql-clients, also preferred self compiled. i know that is possible to compile at every workstation the sourcecode of the server to get a psql-client. but the side effect is, that in this case i have a potential server at every workstation and in my opinion that is a very large footprint. i have to compile psql for myself because otherwise (installing binaries with packaging tools) i have no (or minimal) control about the version of the client. i think that is a use case which can be applied to the needs of many people (for example: oracle offers also client-only-packages which are containing sql+, ...) regards and please excuse my unclear question christian p.s. i don't want to change to RedHatEL/CentOS/Fedora -Ursprüngliche Nachricht- Von: Tomasz Ostrowski [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 7. August 2008 13:01 An: Strobl, Christian Cc: pgsql-general@postgresql.org Betreff: Re: AW: looking for psql without server-installation On 2008-08-07 11:49, [EMAIL PROTECTED] wrote: maybe i misworded my problem. i am looking for the possibility to install a psql client (without a server) out of the sources. It is not a basic problem - it is your solution for a problem, which maybe does have another, better solution. i have to compile psql for myself because otherwise (installing binaries with packaging tools) i have no (or minimal) control about the version of the client. You can use official yum repository and you'll have total control of client version. It works for RedHatEL/CentOS/Fedora: http://yum.pgsqlrpms.org/reporpms/repoview/ Regards Tometzky -- ...although Eating Honey was a very good thing to do, there was a moment just before you began to eat it which was better than when you were... Winnie the Pooh -- 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] looking for psql without server-installation
hallo magnus, thanks for your answer. Just run the make and make install commands in subdirs only. You will need to do it in src/interfaces/libpq and src/bin/psql to get a working psql. that is the answer i was looking for. But you can certainly have control over the version on the client even if you use binary packages like .RPMs. Just decide which version you install. The OS may come with a pre-selected version by default, but for most OSes there are RPMs or DEBs available for other versions as well that you can install. the available versions are very different for every os, every version of the os, and so on. so i don't prefer this method but sometimes (or for other people maybe often) it works best regards christian -- 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] bytea encode performance issues
On Thu, Aug 7, 2008 at 1:16 AM, Sim Zacks [EMAIL PROTECTED] wrote: I don't quite follow that...the whole point of utf8 encoded database is so that you can use text functions and operators without the bytea treatment. As long as your client encoding is set up properly (so that data coming in and out is computed to utf8), then you should be ok. Dropping to ascii is usually not the solution. Your data inputting application should set the client encoding properly and coerce data into the unicode text type...it's really the only solution. Email does not always follow a specific character set. I have tried converting the data that comes in to utf-8 and it does not always work. We receive Hebrew emails which come in mostly 2 flavors, UTF-8 and windows-1255. Unfortunately, they are not compatible with one another. SQL-ASCII and ASCII are different as someone on the list pointed out to me. According to the documentation, SQL-ASCII makes no assumption about encoding, so you can throw in any encoding you want. no, you can't! SQL-ASCII means that the database treats everything like ascii. This means that any operation that deals with text could (and in the case of Hebrew, almost certianly will) be broken. Simple things like getting the length of a string will be wrong. If you are accepting unicode input, you absolutely must be using a unicode encoded backend. If you are accepting text of different encodings from the client, you basically have two choices: a) set client_encoding on the fly to whatever text the client is encoded in b) pick an encoding (utf8) and convert all text to that before sending it to the database (preferred) you pretty much have to go with option 'b' if you are accepting any text for which there is no supported client encoding translation. 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] Create Table Dinamic
Read about dynamic sql in Postgres documentation (EXECUTE statement): http://www.postgresql.org/docs/8.2/static/plpgsql-statements.html Igor -Original Message- From: Anderson dos Santos Donda [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 06, 2008 9:10 PM To: pgsql-general@postgresql.org Subject: Create Table Dinamic Hello All! Its my first time here in maillist and I started work with postgre on last moth. My questions is: Threre is a way to create tables dinamic? Example: To create a table we use CREATE TABLE TableName .. In my db, I have many tables with diferents names but with same colums Example: TableOne ( id int, name text ); TableTwo ( id int, name text ); TableThree ( id int, name text ); So, I created a function to create me this tables with diferents names CREATE OR REPLACE FUNCTION MakeTables ( NameTable text ) RETURNS VOID $$ BEGIN CREATE TABLE NameTable ( id int, name text ); END; $$ LANGUAGE 'plpgsql'; But, the plpgsql or postgre don't accept this.. So, How can I create a table with my function? Thanks for any helps!!! PS : If somebody want knows why I need to create this function, is because in my db have 1000 tables with the some colums, and each time I have a new client, I need to create this tables manually. -- 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] bytea encode performance issues
On Thu, Aug 7, 2008 at 9:38 AM, Merlin Moncure [EMAIL PROTECTED] wrote: On Thu, Aug 7, 2008 at 1:16 AM, Sim Zacks [EMAIL PROTECTED] wrote: I don't quite follow that...the whole point of utf8 encoded database is so that you can use text functions and operators without the bytea treatment. As long as your client encoding is set up properly (so that data coming in and out is computed to utf8), then you should be ok. Dropping to ascii is usually not the solution. Your data inputting application should set the client encoding properly and coerce data into the unicode text type...it's really the only solution. Email does not always follow a specific character set. I have tried converting the data that comes in to utf-8 and it does not always work. We receive Hebrew emails which come in mostly 2 flavors, UTF-8 and windows-1255. Unfortunately, they are not compatible with one another. SQL-ASCII and ASCII are different as someone on the list pointed out to me. According to the documentation, SQL-ASCII makes no assumption about encoding, so you can throw in any encoding you want. no, you can't! SQL-ASCII means that the database treats everything like ascii. This means that any operation that deals with text could (and in the case of Hebrew, almost certianly will) be broken. Simple things like getting the length of a string will be wrong. If you are accepting unicode input, you absolutely must be using a unicode encoded backend. er, I see the problem (single piece of text with multiple encodings inside) :-). ok, it's more complicated than I thought. still, you need to convert the email to utf8. There simply must be a way, otherwise your emails are not well defined. This is a client side problem...if you push it to the server in ascii, you can't use any server side text operations reliably. merlin 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] Create Table Dinamic
On Wed, Aug 6, 2008 at 11:59 PM, Craig Ringer [EMAIL PROTECTED]wrote: Anderson dos Santos Donda wrote: PS : If somebody want knows why I need to create this function, is because in my db have 1000 tables with the some colums, and each time I have a new client, I need to create this tables manually. While EXECUTE or CREATE TABLE ... LIKE is the answer to the immediate question, I have to ask: Is this really the best approach? This is a bit of a design red flag, you see. Is it possible that rather than: CREATE TABLE x_client1( x_client1_id SERIAL PRIMARY KEY, name TEXT ); CREATE TABLE x_client2( x_client2_id SERIAL PRIMARY KEY, name TEXT ); CREATE TABLE x_client3( x_client3_id SERIAL PRIMARY KEY, name TEXT ); ... etc, you might be better off with: CREATE TABLE client ( client_id SERIAL PRIMARY KEY, client_name TEXT NOT NULL -- etc ); CREATE TABLE x ( x_id SERIAL NOT NULL PRIMARY KEY, client_id INTEGER NOT NULL, FOREIGN KEY (client_id) REFERENCES client(client_id) ON DELETE CASCADE, -- Then the fields from the template table for `x': name TEXT, -- etc ); CREATE INDEX x_client_id_fkey ON x (client_id); ? If you are separating the tables for better control over priveleges or the like, might it be better to create a new database instance per client instead? Of course, there are certainly cases where templated tables make sense. In particular, if you need some resources shared between all users, but other resources to be restricted by database permissions to be private to each user, then cloned tables make sense. Putting them in per-user schema keeps things clean and lets you use the schema search path rather than lots of ugly table name pre/suffixes if you have to do this. -- Craig Ringer Thanks all for Help, and answer Craig... Each client has a db, and each db has the same tables. I don't need to share datas with the clients ( and I can't do it ) , because each clients have differents datas in yours tables. My function is to help me to create a new db with the tables. I have a particular server db with the names tables of each client, so I want to do a FOR LOOP in my function to create my tables on new database. Example CREATE OR REPLACE FUNCTION CreateTriggersFunctionsSetValues() RETURNS VOID AS $BODY$ DECLARE NumRowsQuotes ativos%ROWTYPE; NameTable text := ''; BEGIN FOR NumRowsQuotes IN SELECT * FROM ativos LOOP SELECT INTO NameTable ativos WHERE codigo = NumRowsQuotes; setvalues(NameTable); END LOOP; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; If there a better way to do it .. I thanks to tell me!! Any way.. thanks for Help!!
[GENERAL] How to use postgresql-jdbc rpm with Sun JDK
What's the easiest RPM-based way to install postgresql-jdbc on CentOS 5? I just became aware of the JPackage project, which seems appealing, but it doesn't have an up-to-date version of postgresql-jdbc. I'm using Devrim GÜNDÜZ's very nice PostgreSQL repository (pgsqlrpms.org) with yum. When yum installs the PGDG postgresql-jdbc-8.3.3 RPM on CentOS 5, it appears to want to drag in GCJ dependencies, but I want to use a Sun JDK. The JDK is pre-installed by the Rocks V cluster distribution, which is based on CentOS 5. Thanks, Kevin Murphy -- 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 postgresql-jdbc rpm with Sun JDK
On Thu, 2008-08-07 at 09:57 -0400, Kevin Murphy wrote: What's the easiest RPM-based way to install postgresql-jdbc on CentOS 5? I just became aware of the JPackage project, which seems appealing, but it doesn't have an up-to-date version of postgresql-jdbc. I don't think that there is an RPM way to do it, at least for now. See below: When yum installs the PGDG postgresql-jdbc-8.3.3 RPM on CentOS 5, it appears to want to drag in GCJ dependencies, but I want to use a Sun JDK. The JDK is pre-installed by the Rocks V cluster distribution, which is based on CentOS 5. Upstream (I mean, Tom) is building -jdbc package with open source components for a long time, and I am following that, too. We were directly installing the binary jar files under /usr/share/java without compilation. So, you will need to install jar files I think: http://jdbc.postgresql.org/download.html#supported Regards, -- Devrim GÜNDÜZ 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] pg_restore fails on Windows
Hello, We have a very strange problem when restoring a database on Windows XP. The PG version is 8.1.10 The backup was made with the pg_dump on the same machine. pg_restore -F c -h localhost -p 5432 -U postgres -d configV3 -v c:\Share\POSTGRES.backup pg_restore: connecting to database for restore Password: pg_restore: creating SCHEMA public pg_restore: creating COMMENT SCHEMA public pg_restore: creating PROCEDURAL LANGUAGE plpgsql pg_restore: creating SEQUENCE hi_value pg_restore: executing SEQUENCE SET hi_value pg_restore: creating TABLE hibconfigelement pg_restore: creating TABLE hibrefconfigbase pg_restore: creating TABLE hibrefconfigreference pg_restore: creating TABLE hibtableattachment pg_restore: creating TABLE hibtableattachmentxmldata pg_restore: creating TABLE hibtableelementversion pg_restore: creating TABLE hibtableelementversionxmldata pg_restore: creating TABLE hibtablerootelement pg_restore: creating TABLE hibtablerootelementxmldata pg_restore: creating TABLE hibtableunversionedelement pg_restore: creating TABLE hibtableunversionedelementxmldata pg_restore: creating TABLE hibtableversionedelement pg_restore: creating TABLE hibtableversionedelementxmldata pg_restore: creating TABLE versionedelement_history pg_restore: creating TABLE versionedelement_refs pg_restore: restoring data for table hibconfigelement pg_restore: restoring data for table hibrefconfigbase pg_restore: restoring data for table hibrefconfigreference pg_restore: restoring data for table hibtableattachment pg_restore: restoring data for table hibtableattachmentxmldata pg_restore: [archiver (db)] could not execute query: no result from server pg_restore: *** aborted because of error The restore unexpectedly fails on hibtableattachmentxmldata table, which is as follows: CREATE TABLE hibtablerootelementxmldata ( xmldata_id varchar(255) NOT NULL, xmldata text ) WITHOUT OIDS; and contains thousands of rows with text field having even 40MB, encoded in UTF8. The database is created as follows: CREATE DATABASE configV3 WITH OWNER = postgres ENCODING = 'UTF8' TABLESPACE = pg_default; The really strange is that the db restore runs OK on linux (tested on RHEL4, PG version 8.1.9). The pg_restore output is _not_ very descriptive but I suspect some dependency on OS system libraries (encoding), or maybe it is also related to the size of the CLOB field. Anyway we are now effectively without any possibility to backup our database, which is VERY serious. Have you ever came across something similar to this? Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] bytea encode performance issues
Merlin Moncure escribió: er, I see the problem (single piece of text with multiple encodings inside) :-). ok, it's more complicated than I thought. still, you need to convert the email to utf8. There simply must be a way, otherwise your emails are not well defined. This is a client side problem...if you push it to the server in ascii, you can't use any server side text operations reliably. I think the solution is to get the encoding from the email header and the set the client_encoding to that. However, as soon as an email with an unsopported encoding comes by, you are stuck. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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] bytea encode performance issues
Merlin, You are suggesting a fight with the flexible dynamics of email by fitting it into a UTF shell - it doesn't always work. I would suggest you read the postgresql definition of SQL-ASCII: The SQL_ASCII setting behaves considerably differently from the other settings. When the server character set is SQL_ASCII, the server interprets byte values 0-127 according to the ASCII standard, while byte values 128-255 are taken as uninterpreted characters. No encoding conversion will be done when the setting is SQL_ASCII. Thus, this setting is not so much a declaration that a specific encoding is in use, as a declaration of ignorance about the encoding. In most cases, if you are working with any non-ASCII data, it is unwise to use the SQL_ASCII setting, because PostgreSQL will be unable to help you by converting or validating non-ASCII characters. It says, In most cases it is unwise to use it if you are working with non-ascii data. That is because most situations do not accept multiple encodings. However, email is a special case where the user does not have control of what is being sent. Therefore it is possible (and it happens to us) that we get emails that are not convertible to UTF-8. The only way I could convert from mysql, which does not check encoding to postgresql utf-8 was to first use the SQL-ASCII database as a bridge, because it did not check the encoding and load it into a bytea and then take a backup of the database and restore it into a UTF-8 database. Sim Merlin Moncure wrote: On Thu, Aug 7, 2008 at 9:38 AM, Merlin Moncure [EMAIL PROTECTED] wrote: On Thu, Aug 7, 2008 at 1:16 AM, Sim Zacks [EMAIL PROTECTED] wrote: I don't quite follow that...the whole point of utf8 encoded database is so that you can use text functions and operators without the bytea treatment. As long as your client encoding is set up properly (so that data coming in and out is computed to utf8), then you should be ok. Dropping to ascii is usually not the solution. Your data inputting application should set the client encoding properly and coerce data into the unicode text type...it's really the only solution. Email does not always follow a specific character set. I have tried converting the data that comes in to utf-8 and it does not always work. We receive Hebrew emails which come in mostly 2 flavors, UTF-8 and windows-1255. Unfortunately, they are not compatible with one another. SQL-ASCII and ASCII are different as someone on the list pointed out to me. According to the documentation, SQL-ASCII makes no assumption about encoding, so you can throw in any encoding you want. no, you can't! SQL-ASCII means that the database treats everything like ascii. This means that any operation that deals with text could (and in the case of Hebrew, almost certianly will) be broken. Simple things like getting the length of a string will be wrong. If you are accepting unicode input, you absolutely must be using a unicode encoded backend. er, I see the problem (single piece of text with multiple encodings inside) :-). ok, it's more complicated than I thought. still, you need to convert the email to utf8. There simply must be a way, otherwise your emails are not well defined. This is a client side problem...if you push it to the server in ascii, you can't use any server side text operations reliably. merlin 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] Response time between shared buffer cache and operating system
I am using Postgres 8.1.4 on Linux. I am interested in the calculating the following for a specific application: How long it takes the operating system to fulfil a page demand, ie, reading the page from disk or from the OS cache to the Postgres shared buffer. Also how long it takes the bgwriter to flush a page from the shared buffer into the OS cache or disk. These can be averages or detailed info that I can analyze with other tools Are there any functions/views available that log this information, if not how should I change the source code. _ Win New York holidays with Kellogg’s Live Search http://clk.atdmt.com/UKM/go/107571440/direct/01/
Re: [GENERAL] pg_restore fails on Windows
Tom Tom wrote: Hello, We have a very strange problem when restoring a database on Windows XP. The PG version is 8.1.10 The backup was made with the pg_dump on the same machine. pg_restore -F c -h localhost -p 5432 -U postgres -d configV3 -v c:\Share\POSTGRES.backup pg_restore: connecting to database for restore Password: pg_restore: creating SCHEMA public pg_restore: creating COMMENT SCHEMA public pg_restore: creating PROCEDURAL LANGUAGE plpgsql pg_restore: creating SEQUENCE hi_value pg_restore: executing SEQUENCE SET hi_value pg_restore: creating TABLE hibconfigelement pg_restore: creating TABLE hibrefconfigbase pg_restore: creating TABLE hibrefconfigreference pg_restore: creating TABLE hibtableattachment pg_restore: creating TABLE hibtableattachmentxmldata pg_restore: creating TABLE hibtableelementversion pg_restore: creating TABLE hibtableelementversionxmldata pg_restore: creating TABLE hibtablerootelement pg_restore: creating TABLE hibtablerootelementxmldata pg_restore: creating TABLE hibtableunversionedelement pg_restore: creating TABLE hibtableunversionedelementxmldata pg_restore: creating TABLE hibtableversionedelement pg_restore: creating TABLE hibtableversionedelementxmldata pg_restore: creating TABLE versionedelement_history pg_restore: creating TABLE versionedelement_refs pg_restore: restoring data for table hibconfigelement pg_restore: restoring data for table hibrefconfigbase pg_restore: restoring data for table hibrefconfigreference pg_restore: restoring data for table hibtableattachment pg_restore: restoring data for table hibtableattachmentxmldata pg_restore: [archiver (db)] could not execute query: no result from server pg_restore: *** aborted because of error The restore unexpectedly fails on hibtableattachmentxmldata table, which is as follows: CREATE TABLE hibtablerootelementxmldata ( xmldata_id varchar(255) NOT NULL, xmldata text ) WITHOUT OIDS; and contains thousands of rows with text field having even 40MB, encoded in UTF8. The database is created as follows: CREATE DATABASE configV3 WITH OWNER = postgres ENCODING = 'UTF8' TABLESPACE = pg_default; The really strange is that the db restore runs OK on linux (tested on RHEL4, PG version 8.1.9). The pg_restore output is _not_ very descriptive but I suspect some dependency on OS system libraries (encoding), or maybe it is also related to the size of the CLOB field. Anyway we are now effectively without any possibility to backup our database, which is VERY serious. Have you ever came across something similar to this? Check what you have in your server logs (pg_log directory) and the eventlog around this time. There is probably a better error message available there. //Magnus -- 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] looking for psql without server-installation
Christian Strobl wrote: thanks for your answers. unfortunately i misworded my problem again. i have a very clear problem and i am looking for a solution of this problem (if it is basic or not) and i am not using RedHatEL/CentOS/Fedora. i am looking for a solution which is applicable to every linux os and not only for some. so here again my problem: i am looking for the possibility to install a psql client (without a server) FROM THE SOURCE. my usecase is: one server with a postgresql-server (self compiled) and several workstations with psql-clients, also preferred self compiled. i know that is possible to compile at every workstation the sourcecode of the server to get a psql-client. but the side effect is, that in this case i have a potential server at every workstation and in my opinion that is a very large footprint. i have to compile psql for myself because otherwise (installing binaries with packaging tools) i have no (or minimal) control about the version of the client. i think that is a use case which can be applied to the needs of many people (for example: oracle offers also client-only-packages which are containing sql+, ...) regards and please excuse my unclear question christian p.s. i don't want to change to RedHatEL/CentOS/Fedora Unless I understood you wrong, this is the solution: - Download the source, unpack it and follow the installation procedure described in http://www.postgresql.org/docs/current/static/install-procedure.html - Pay specific attention to the section Client-only installation: in point 4. First, read http://www.postgresql.org/docs/current/static/install-requirements.html Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to use postgresql-jdbc rpm with Sun JDK
Devrim GÜNDÜZ wrote: On Thu, 2008-08-07 at 09:57 -0400, Kevin Murphy wrote: When yum installs the PGDG postgresql-jdbc-8.3.3 RPM on CentOS 5, it appears to want to drag in GCJ dependencies, but I want to use a Sun JDK. The JDK is pre-installed by the Rocks V cluster distribution, which is based on CentOS 5. Upstream (I mean, Tom) is building -jdbc package with open source components for a long time, and I am following that, too. We were directly installing the binary jar files under /usr/share/java without compilation. So, you will need to install jar files I think: http://jdbc.postgresql.org/download.html#supported Speaking as a near-ignoramus, would a simple RPM that wraps the binary jar file make sense? I'm not sure what dependencies it should have, though: simply 'java'? My issue is that a Rocks cluster likes to have all software packaged as RPMs; compute nodes can be automatically built from scratch when required simply by dumping RPMs in a certain directory on the head node and adding the RPM name to an XML file. I guess it can't be that hard for me to custom-build this sort of RPM, although I've never built one before. -Kevin Murphy -- 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 postgresql-jdbc rpm with Sun JDK
Kevin Murphy [EMAIL PROTECTED] writes: Devrim GÃNDÃZ wrote: Upstream (I mean, Tom) is building -jdbc package with open source components for a long time, and I am following that, too. We were directly installing the binary jar files under /usr/share/java without compilation. Speaking as a near-ignoramus, would a simple RPM that wraps the binary jar file make sense? Sure, if you want to do it that way. We did in fact do it that way up till about 8.0. We (or at least I) moved away from it because of Red Hat's policy that source RPMs should contain only, well, source. But if you roll your own you certainly need not be bound by that ideology. 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] GUI for master-slave?
Since it supports master-slave with Slony, has anyone had good experience using phpPgAdmin for master-slave? Not much in docs... On Wed, Aug 6, 2008 at 2:59 PM, Bill Wordsworth [EMAIL PROTECTED] wrote: Is there any GUI for master-slave (I prefer CLI too)? Are there any PostgreSQL-specific detailed videos that clearly illustrate replication, PITR, failover, load-balance, backup etc (I need it for a pitch)? Any recommended commercial off-the-shelf/plug-and-play type products or (hosted? (SimpleDB?)) services for this? Does NAVICAT do master-slave? Cheers, Bill -- 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] Initdb problem on debian mips cobalt: Bus error
The conclusion of the thread seemed to be that it was a linker or assembler problem triggered by our use of SUBSYS.o files to aggregate all the backend .o files into a smaller number of files for the final link. If so the answer is either (a) update to a newer toolchain that might fix the problem, or (b) try our CVS HEAD which doesn't use the SUBSYS.o trick anymore. Mind you, I'd not especially recommend trying to run CVS HEAD for production purposes, but it would be real interesting at this point to see if you can compile it and run the regression tests with the toolchain you've got. So tonight I've built CVS HEAD and rerun make check. All 115 tests passed! It works :-) __ Not happy with your email address?. Get the one you really want - millions of new email addresses available now at Yahoo! http://uk.docs.yahoo.com/ymail/new.html -- 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] Initdb problem on debian mips cobalt: Bus error
Glyn Astill [EMAIL PROTECTED] writes: The conclusion of the thread seemed to be that it was a linker or assembler problem triggered by our use of SUBSYS.o files to aggregate all the backend .o files into a smaller number of files for the final link. If so the answer is either (a) update to a newer toolchain that might fix the problem, or (b) try our CVS HEAD which doesn't use the SUBSYS.o trick anymore. So tonight I've built CVS HEAD and rerun make check. All 115 tests passed! It works :-) Okay, so it is indeed the linker's fault. Now try plan (a) --- can you find a more up-to-date toolchain? 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] Postgres 8.3.x installation on Fedora 9 system
Pardon a dumb question. Installing Postgres 7.x on FC4-6, I would install a large set of RPMs, these I think: postgresql postgresql-contrib postgresql-devel postgresql-jdbc postgresql-libs postgresql-python postgresql-server I then had everything I need to run postgres and access it from Java and Python (through the pygresql driver). I'm now trying to move to 8.3.x on Fedora 9. Using pgdg-83-fedora.repo, yum finds two RPMs, postgresql-8.3.3-1PGDG.f9.i386 and postgresql-libs-8.3.3-1PGDG.f9.i386. I checked postgresql.org, but the fedora 9 directories are empty, (unlike the f7 and f8 directories). I know that the JDBC driver is a separate project, but I'm having trouble getting python access to work. I downloaded PyGreSQL-3.8.tgz, but cannot install it because it relies on pg_config, which is not present the the 8.3.3 RPMs I installed. Am I just on the wrong path here, starting with the postgresql and postgresql-libs RPMs? Jack Orenstein -- 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 8.3.x installation on Fedora 9 system
Jack Orenstein [EMAIL PROTECTED] writes: Pardon a dumb question. Installing Postgres 7.x on FC4-6, I would install a large set of RPMs, these I think: postgresql postgresql-contrib postgresql-devel postgresql-jdbc postgresql-libs postgresql-python postgresql-server So ... why didn't you just yum install the same ones on F-9? 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] compat-postgresql-libs rpm bug in 64bit mode
On Wed, 2008-08-06 at 13:01 -0400, Kevin Murphy wrote: The package creates /usr/lib64/libpq.so.4.1, but it does not create /usr/lib64/libpq.so.4 - /usr/lib64/libpq.so.4.1. This is a bit strange: [EMAIL PROTECTED] ~]# ll /usr/lib64/libpq.so.4.1 -rwxr-xr-x 1 root root 14 Jan 12 2008 /usr/lib64/libpq.so.4.1 [EMAIL PROTECTED] ~]# ll /usr/lib64/libpq.so.4 lrwxrwxrwx 1 root root 23 Aug 7 14:11 /usr/lib64/libpq.so.4 - /usr/lib64/libpq.so.4.1 Symlinking is done after installing package with a %post operation. I suggest you to create symlinks for yourself. -- Devrim GÜNDÜZ 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] Postgres 8.3.x installation on Fedora 9 system
Tom Lane wrote: Jack Orenstein [EMAIL PROTECTED] writes: Pardon a dumb question. Installing Postgres 7.x on FC4-6, I would install a large set of RPMs, these I think: postgresql postgresql-contrib postgresql-devel postgresql-jdbc postgresql-libs postgresql-python postgresql-server So ... why didn't you just yum install the same ones on F-9? Can't find them. They aren't on postgresql.org (e.g. http://www.postgresql.org/ftp/binary/v8.3.3/linux/rpms/fedora/fedora-9-i386/), and apparently not on pgdg-83-fedora.repo. Jack -- 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 8.3.x installation on Fedora 9 system
On Thu, 2008-08-07 at 17:24 -0400, Jack Orenstein wrote: Pardon a dumb question. Installing Postgres 7.x on FC4-6, I would install a large set of RPMs, these I think: postgresql postgresql-contrib postgresql-devel postgresql-jdbc postgresql-libs postgresql-python postgresql-server I then had everything I need to run postgres and access it from Java and Python (through the pygresql driver). I'm now trying to move to 8.3.x on Fedora 9. Using pgdg-83-fedora.repo, yum finds two RPMs, postgresql-8.3.3-1PGDG.f9.i386 and postgresql-libs-8.3.3-1PGDG.f9.i386. I checked postgresql.org, but the fedora 9 directories are empty, (unlike the f7 and f8 directories). I know that the JDBC driver is a separate project, but I'm having trouble getting python access to work. I downloaded PyGreSQL-3.8.tgz, but cannot install it because it relies on pg_config, which is not present the the 8.3.3 RPMs I installed. Am I just on the wrong path here, starting with the postgresql and postgresql-libs RPMs? Fedora 9 has it's own postgres 8.3.3 packages as part of base... # rpm -qa|grep postgres mono-data-postgresql-1.9.1-2.fc9.i386 postgresql-odbc-08.03.0100-1.fc9.i386 postgresql-server-8.3.3-2.fc9.i386 postgresql-python-8.3.3-2.fc9.i386 postgresql-devel-8.3.3-2.fc9.i386 postgresql-8.3.3-2.fc9.i386 postgresql-libs-8.3.3-2.fc9.i386 Not sure why you feel the need to go to a 3rd party Craig -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] restoring one table?
We need to restore one table from a backup. What is the proper way to do this? Our backup command looks like this: pg_dump -C -Fc -S postgresql mydatabase today.backup.sqlc I am using PostgreSQL 8.1. -- 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 8.3.x installation on Fedora 9 system
Jack Orenstein [EMAIL PROTECTED] writes: Tom Lane wrote: So ... why didn't you just yum install the same ones on F-9? Can't find them. You didn't look in the standard Fedora repositories? 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] Floating-point software assist fault?
We're seeing gobs of these via dmesg in PostgreSQL 8.3.3 on ia64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-8), kernel 2.6.9-55.EL: postmaster(13144): floating-point assist fault at ip 403a9382, isr 0408 It appears to be an Itanium-specific issue with floating-point normalization, here is a document describing the issue. http://i-cluster2.inrialpes.fr/doc/misc/fpswa.txt “The Intel Itanium does not fully support IEEE denormals and requires software assistance to handle them. Without further informations, the ia64 GNU/Linux kernel triggers a fault when denormals are computed. This is the floating-point software assist fault (FPSWA) in the kernel messages. It is the user's task to clearly design his program to prevent such cases.” “To conclude, I'd like to stress the fact that the programmer has to be careful when dealing with floating-point numbers. Even with high precision, it is easy to produce denormals and get strange behaviour.” Any thoughts? TIA. Ed -- 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] Floating-point software assist fault?
On Thursday 08/07/08 @ 5:43 pm MDT, I received this from Ed L. [EMAIL PROTECTED]: We're seeing gobs of these via dmesg in PostgreSQL 8.3.3 on ia64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-8), kernel 2.6.9-55.EL: postmaster(13144): floating-point assist fault at ip 403a9382, isr 0408 These are coming lately exclusively from the writer process... TIA. Ed -- 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] Floating-point software assist fault?
On Thursday 08/07/08 @ 5:46 pm MDT, I received this from Ed L. postmaster(13144): floating-point assist fault at ip 403a9382, isr 0408 These are coming lately exclusively from the writer process... Actually, the machine has been up for 45 days and dmesg doesn't have timestamps, so I'm not sure if those pids have any relation to the ones currently in use. Ed -- 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] Floating-point software assist fault?
Ed L. [EMAIL PROTECTED] writes: We're seeing gobs of these via dmesg in PostgreSQL 8.3.3 on ia64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-8), kernel 2.6.9-55.EL: postmaster(13144): floating-point assist fault at ip 403a9382, isr 0408 See if you can trace that instruction pointer address to any specific part of the PG code (gdb will help, if the execute is built --enable-debug). PG isn't intentionally dealing in denormals, but I wonder if you've found an edge case in, say, the code to manage spread-out checkpoints. I believe that does do FP arithmetic, and it's in the bgwriter ... 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] bytea encode performance issues
Alvaro Herrera wrote: Merlin Moncure escribió: er, I see the problem (single piece of text with multiple encodings inside) :-). ok, it's more complicated than I thought. still, you need to convert the email to utf8. There simply must be a way, otherwise your emails are not well defined. This is a client side problem...if you push it to the server in ascii, you can't use any server side text operations reliably. I think the solution is to get the encoding from the email header and the set the client_encoding to that. However, as soon as an email with an unsopported encoding comes by, you are stuck. Why not leave it as bytea? The postgres server has no encoding problems with storing whatever you want to throw at it, postgres client has no problem reading it back. It's then up to the imap/pop3/whatever client to deal with it. That's normally the way the email server world works. FWIW the RFC for email (822/2822) says it is all ASCII so it's not a problem at all as long as every email generator follows the IETF rules (body here is not just the text of the message - its the data after the blank line in the SMTP conversation until the CRLF.CRLF). 2.3. Body The body of a message is simply lines of US-ASCII characters. The 2 things that will make a difference to the query is 1. get rid of the encode call and 2. stop it being toasted Assuming that the dbmail code can't be changed yet 1. make encode a no-op. - create schema foo; - create function foo.encode (bytea,text) returns bytea as $$ select $1 $$ language sql immutable; - change postgresql.conf search_path to foo,pg_catalog, This completly breaks encode so if anything uses it properly then it's broken that. From the query we've seen, we don't know if it's needed or not. What query do you get if you search for something that has utf or other encoding non-ASCII characters? If it looks like the output of escape (i.e. client used PQescapeByteaConn on the search text), then the escape might be required. 2. dbmail already chunks email up into ~500k blocks. If that is a configurable setting, turn it down to about 1.5k blocks. klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- 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] bytea encode performance issues
On Aug 7, 2008, at 5:28 PM, Klint Gore wrote: Alvaro Herrera wrote: Merlin Moncure escribió: er, I see the problem (single piece of text with multiple encodings inside) :-). ok, it's more complicated than I thought. still, you need to convert the email to utf8. There simply must be a way, otherwise your emails are not well defined. This is a client side problem...if you push it to the server in ascii, you can't use any server side text operations reliably. I think the solution is to get the encoding from the email header and the set the client_encoding to that. However, as soon as an email with an unsopported encoding comes by, you are stuck. Why not leave it as bytea? The postgres server has no encoding problems with storing whatever you want to throw at it, postgres client has no problem reading it back. It's then up to the imap/ pop3/whatever client to deal with it. That's normally the way the email server world works. FWIW the RFC for email (822/2822) says it is all ASCII so it's not a problem at all as long as every email generator follows the IETF rules (body here is not just the text of the message - its the data after the blank line in the SMTP conversation until the CRLF.CRLF). That's not actually true for email, though. Content-Transfer-Encoding: 8bit, combined with ESMTP 8BITMIME, for example. So, yeah, you're right. Generally, email is too complex to deal with in the database as anything other than an opaque bytea blob, along with some metadata (that metadata might well include text fields that contain the text content of the mail, for search, for instance). Another option is to convert non-ascii mail to ascii before storing it, but that's not as trivial as it sounds. You cannot convert mail, in general, to utf8, as not all mail content is textual. The 2 things that will make a difference to the query is 1. get rid of the encode call and 2. stop it being toasted Assuming that the dbmail code can't be changed yet 1. make encode a no-op. - create schema foo; - create function foo.encode (bytea,text) returns bytea as $$ select $1 $$ language sql immutable; - change postgresql.conf search_path to foo,pg_catalog, This completly breaks encode so if anything uses it properly then it's broken that. From the query we've seen, we don't know if it's needed or not. What query do you get if you search for something that has utf or other encoding non-ASCII characters? If it looks like the output of escape (i.e. client used PQescapeByteaConn on the search text), then the escape might be required. 2. dbmail already chunks email up into ~500k blocks. If that is a configurable setting, turn it down to about 1.5k blocks. Cheers, Steve -- 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] Create Table Dinamic
try whit this http://www.postgresql.org/docs/8.3/interactive/ecpg-dynamic.html --- On Thu, 8/7/08, Anderson dos Santos Donda [EMAIL PROTECTED] wrote: From: Anderson dos Santos Donda [EMAIL PROTECTED] Subject: [GENERAL] Create Table Dinamic To: pgsql-general@postgresql.org Date: Thursday, August 7, 2008, 1:10 AM Hello All! Its my first time here in maillist and I started work with postgre on last moth. My questions is: Threre is a way to create tables dinamic? Example: To create a table we use CREATE TABLE TableName .. In my db, I have many tables with diferents names but with same colums Example: TableOne ( id int, name text ); TableTwo ( id int, name text ); TableThree ( id int, name text ); So, I created a function to create me this tables with diferents names CREATE OR REPLACE FUNCTION MakeTables ( NameTable text ) RETURNS VOID $$ BEGIN CREATE TABLE NameTable ( id int, name text ); END; $$ LANGUAGE 'plpgsql'; But, the plpgsql or postgre don't accept this.. So, How can I create a table with my function? Thanks for any helps!!! PS : If somebody want knows why I need to create this function, is because in my db have 1000 tables with the some colums, and each time I have a new client, I need to create this tables manually. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
