[GENERAL] mild modification to pg_dump
I would need to do a mild change to pg_dump, working against a 9.4 server on linux. Which source tree do I need? Have gcc 4.9.2 in my Lubuntu installation. TIA -- 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] mild modification to pg_dump
Sorry, I was not exact. I don't need nor like to change pg_dump. Rather, based on pg_dump code, I need to develop a daemon which can receive a TCP message (from a privileged app) containing some elements: the database to dump, the user under which do that, and his password. (My apps are using that same data, of course, encripted to the common users). Thank you, Scott. On 17/11/17 10:49, Scott Mead wrote: On Fri, Nov 17, 2017 at 7:51 AM, marcelo <mailto:marcelo.nico...@gmail.com>> wrote: I would need to do a mild change to pg_dump, working against a 9.4 server on linux. Which source tree do I need? Have gcc 4.9.2 in my Lubuntu installation. TIA What exactly do you need to change? Most likely, there is a quick and easy fix for whatever you're doing without modifying pg_dump itself. That being said, if you really want to modify the source, download the source tarball: https://www.postgresql.org/ftp/source/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org <mailto:pgsql-general@postgresql.org>) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general <http://www.postgresql.org/mailpref/pgsql-general> -- -- Scott Mead Sr. Architect /OpenSCG <http://openscg.com>/ http://openscg.com
Re: [GENERAL] mild modification to pg_dump
I need to "emulate" the pg_dump code because the password prompt. Years ago I write a program (for the QnX environment) that catched some prompt and emulates the standard input. I don't like to do that again. On 17/11/17 17:23, John R Pierce wrote: On 11/17/2017 12:19 PM, marcelo wrote: Sorry, I was not exact. I don't need nor like to change pg_dump. Rather, based on pg_dump code, I need to develop a daemon which can receive a TCP message (from a privileged app) containing some elements: the database to dump, the user under which do that, and his password. (My apps are using that same data, of course, encripted to the common users). I would just fork pg_dump to do the actual dump rather than try and incorporate its source code into your app. -- 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] mild modification to pg_dump
I will give expect a try. But the source code embedded in my daemon. On 17/11/17 17:49, Ron Johnson wrote: On 11/17/2017 02:23 PM, John R Pierce wrote: On 11/17/2017 12:19 PM, marcelo wrote: Sorry, I was not exact. I don't need nor like to change pg_dump. Rather, based on pg_dump code, I need to develop a daemon which can receive a TCP message (from a privileged app) containing some elements: the database to dump, the user under which do that, and his password. (My apps are using that same data, of course, encripted to the common users). I would just fork pg_dump to do the actual dump rather than try and incorporate its source code into your app. Specifically, do you mean to write a simple daemon which forks pg_dump at the appropriate time? -- 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] mild modification to pg_dump
Thank you, Scott. That's happening me because incomplete docs reading. Truly, I'm catched in a very big app, so I have no time to read all the docs. On 17/11/17 18:31, Scott Mead wrote: On Fri, Nov 17, 2017 at 4:06 PM, marcelo <mailto:marcelo.nico...@gmail.com>> wrote: I need to "emulate" the pg_dump code because the password prompt. Years ago I write a program (for the QnX environment) that catched some prompt and emulates the standard input. I don't like to do that again. pg_dump can use an environment variable "PGPASSWORD" upon execution (actually, all libpq programs can). You could have a wrapper that sets the environment variable and then executes pg_dump, this would get you around that prompt. Similarly, you could use the .pgpass file. https://www.postgresql.org/docs/9.5/static/libpq-envars.html https://www.postgresql.org/docs/9.5/static/libpq-pgpass.html --Scott On 17/11/17 17:23, John R Pierce wrote: On 11/17/2017 12:19 PM, marcelo wrote: Sorry, I was not exact. I don't need nor like to change pg_dump. Rather, based on pg_dump code, I need to develop a daemon which can receive a TCP message (from a privileged app) containing some elements: the database to dump, the user under which do that, and his password. (My apps are using that same data, of course, encripted to the common users). I would just fork pg_dump to do the actual dump rather than try and incorporate its source code into your app. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org <mailto:pgsql-general@postgresql.org>) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general <http://www.postgresql.org/mailpref/pgsql-general> -- -- Scott Mead Sr. Architect /OpenSCG <http://openscg.com>/ http://openscg.com
Re: [GENERAL] mild modification to pg_dump
Again: knowing of .pgpass (thank you Scott) this is what I will do. On 17/11/17 17:49, Ron Johnson wrote: On 11/17/2017 02:23 PM, John R Pierce wrote: On 11/17/2017 12:19 PM, marcelo wrote: Sorry, I was not exact. I don't need nor like to change pg_dump. Rather, based on pg_dump code, I need to develop a daemon which can receive a TCP message (from a privileged app) containing some elements: the database to dump, the user under which do that, and his password. (My apps are using that same data, of course, encripted to the common users). I would just fork pg_dump to do the actual dump rather than try and incorporate its source code into your app. Specifically, do you mean to write a simple daemon which forks pg_dump at the appropriate time? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Unknown lvalue 'PIDFILE' in section 'Service'
Hi Today I installed postgresql 9.4 on Lubuntu 15.04 from the EnterpriseDB's package. I modified pg_hba.conf to accept connections from the local network, and tried to start the backend. It do not; looking the log, I can see the message I copied to the subject. The full log entry is [/lib/systemd/system/postgresql-9.4.service:12] Unknown lvalue 'PIDFILE' in section 'Service' But the corresponding entry in /lib/systemd/system/postgresql-9.4.service shows PIDFILE=/opt/PostgreSQL/9.4/data/postmaster.pid which is perfectly valid, because the data dir is Environment=PGDATA=/opt/PostgreSQL/9.4/data What is going wrong? TIA Marcelo
Re: [GENERAL] Unknown lvalue 'PIDFILE' in section 'Service'
On 28/06/17 10:17, Adrian Klaver wrote: On 06/28/2017 05:06 AM, marcelo wrote: Hi Today I installed postgresql 9.4 on Lubuntu 15.04 from the EnterpriseDB's package. I modified pg_hba.conf to accept connections from the local network, and tried to start the backend. It do not; looking the log, I can see the message I copied to the subject. The full log entry is [/lib/systemd/system/postgresql-9.4.service:12] Unknown lvalue 'PIDFILE' in section 'Service' But the corresponding entry in /lib/systemd/system/postgresql-9.4.service shows PIDFILE=/opt/PostgreSQL/9.4/data/postmaster.pid which is perfectly valid, because the data dir is Environment=PGDATA=/opt/PostgreSQL/9.4/data What is going wrong? I am no systemd expert by any means, but I have to believe that it should be: Environment=PIDFILE=/opt/PostgreSQL/9.4/data/postmaster.pid Hi Adrian Thank you very much. I tried your "recipe" and it was the solution. But I had to change the pg_hba.conf permissions, from -rw--- 1 root root 4318 jun 28 06:57 data/pg_hba.conf to -rw-r--r-- 1 root root 4318 jun 28 06:57 data/pg_hba.conf I note this, because others may find the same issue. Now, the server is started! Marcelo -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] What is exactly a schema?
The question is not trivial. Could I maintain two or three separate/distinct "versions" of same database using one schema for every of them? Could some tables (in the public schema) be shared among all the schemas? -- 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] What is exactly a schema?
Thank you. Now I have a related question. Could I select a specific schema in the connection string? Say, by example database=mydb.schemanumbertwo ? I'm asking this because I will be using Devart's dotConnect and Entity developer to access the database. I have not full control, so I cannot set the search path immediately after the connection. If the first example is possible, I will replace the schema name on the fly, before connection attempt. TIA On 14/07/17 07:58, Berend Tober wrote: marcelo wrote: The question is not trivial. Could I maintain two or three separate/distinct "versions" of same database using one schema for every of them? Could some tables (in the public schema) be shared among all the schemas? Yes and yes. In the Postgresql world, the word "schema" is maybe unfortunately overloaded, but whenever you read it think "namespace". In fact, in the systems catalog there are columns named "namespace" that store data referring to named schemas. -- B -- 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] What is exactly a schema?
Thank you. I know that. It would be my last resort, because aside, I need that every app user must login to be able to assign logical privileges at the app level. Of course, I will have my own tables of users and roles, independently of the postgres users an roles. I will think of it. On 14/07/17 09:19, Bill Moran wrote: On Fri, 14 Jul 2017 08:59:13 -0300 marcelo wrote: Thank you. Now I have a related question. Could I select a specific schema in the connection string? Say, by example database=mydb.schemanumbertwo ? I'm asking this because I will be using Devart's dotConnect and Entity developer to access the database. I have not full control, so I cannot set the search path immediately after the connection. If the first example is possible, I will replace the schema name on the fly, before connection attempt. I don't think you can do exactly what you're asking. However, you should be able to achieve the same result by setting a default schema for the user that you're connecting as. See the docs for ALTER ROLE and SET. -- 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] What is exactly a schema?
I'll be using Devart's dotConnect. I have two alternatives at this moment a) To set the user name to the required schema. This has the (little) drawback that forces user configuration for every schema... b) To manually do something like the JDBC driver you mention, but it triggers some questions b.1) To execute the set search_path one must be connected, database name included. I think by that time, the default schema is determined. Or I am wrong, am I? b.2) The search_path is valid for the database or restricted to the connection? Thank you On 14/07/17 09:59, Thomas Kellerer wrote: marcelo schrieb am 14.07.2017 um 13:59: Could I select a specific schema in the connection string? Say, by example database=mydb.schemanumbertwo ? The JDBC driver does indeed support that: jdbc:postgresql://localhost/somedatabase?currentSchema=some_schema I think in the backround it then simply runs a set search_path = some_schema; after the connection has been established. -- 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] What is exactly a schema?
I'm sorry. dotConnect for PostgreSql is able to set the schema at connection time. This may be set as part of the connection string, or as a dbconnection class' property. i was in doubt because the version I'm using is somewhat old, but decompiling it shows the property in place. So, I will close this thread. Thanks to all who answered. I acquired some new knowledge. On 14/07/17 13:50, John R Pierce wrote: On 7/14/2017 4:59 AM, marcelo wrote: Now I have a related question. Could I select a specific schema in the connection string? Say, by example database=mydb.schemanumbertwo ? the default search_path is $user,public so if you connect with different SQL usernames for your different schemas, and have all your common tables in PUBLIC, then it will just fall out. you'll need to be careful with permissions, of course. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Schemas and foreign keys
Recently I asked regarding schemas, and received very useful answers. I conclude that I can put some tables in one schema and left others in the public one. If my app selects some schema, the backend will found automatically the absent tables in "public". So far, so good. But what about foreign keys? At least, I will have foreign keys from the tables in the specified schema to the tables in "public", because I'm thinking that the tables in "public" would be references, while the tables residing in the specified schema will be the transactional ones. TIA Marcelo -- 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] Schemas and foreign keys
Thank you, Andreas. Your answer closes this thread. On 21/07/17 11:07, Andreas Kretschmer wrote: Am 21.07.2017 um 14:58 schrieb marcelo: Recently I asked regarding schemas, and received very useful answers. I conclude that I can put some tables in one schema and left others in the public one. If my app selects some schema, the backend will found automatically the absent tables in "public". So far, so good. But what about foreign keys? At least, I will have foreign keys from the tables in the specified schema to the tables in "public", because I'm thinking that the tables in "public" would be references, while the tables residing in the specified schema will be the transactional ones. TIA Marcelo that's no problem: test=# create schema demo1; CREATE SCHEMA test=*# create schema demo2; CREATE SCHEMA test=*# create table master_table(id int primary key); CREATE TABLE test=*# create table demo1.demo_table(id int primary key, master_id int references public.master_table); CREATE TABLE test=*# create table demo2.demo_table(id int primary key, master_id int references public.master_table); CREATE TABLE Regards, Andreas -- 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] Developer GUI tools for PostgreSQL
You can try SQL Manager for PostgreSql. The Lite edition is enough ans it's free. It's fast, secure and very friendly. On 26/07/17 19:41, Tiffany Thang wrote: Hi, I'm new to PostgreSQL. I'm looking for a developer tool that works similarly to TOAD or SQL Developer for Oracle/MySQL which would allow me to view and make DDL changes to database objects and create data models. It would be a plus if I can use the same tool to perform some database administration tasks. So far, I've found TOra and pgAdmin 4. Are there any other popular GUI tools? 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
[GENERAL] Schemas and serials
Some days ago I asked regarding tables located in different schemas. Now, my question is Suppose I have two schemas (other than public): "qa" and "production". Initially I create all my tables in "qa". All of them have a primary key of type serial. Later, I will copy the tables definitions to production. It will automatically create the sequences in the new schema, starting at zero? TIA Marcelo -- 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] Schemas and serials
Melvin: My example was somewhat inexact. The full question is as follows: I need to have two groups of tables: the "reference" ones (examples: city, country, customer) which will "reside" in the public schema, and the transaccional ones, which will reside in a schema representing one year/season. These table's definitions must be copied to a new schema at the start of new year/season. One of these tables create script could be as follows /CREATE TABLE dailyprogram// //(// // id serial NOT NULL,// // date timestamp without time zone NOT NULL,// // packerid integer NOT NULL,// // CONSTRAINT "PK_dailyprogram" PRIMARY KEY (id)// //)// //WITH (// // OIDS=FALSE// //);// //ALTER TABLE dailyprogram// // OWNER TO postgres;// / My reworded question is: if I run this sql in the new schema, the implicit '/CREATE SEQUENCE dailyprogram_id_seq;/' statement will be executed in the new schema, so the sequence will be reset to zero? TIA PS: Of course, I considered the other option: to have a table representing the seasons, and every main transactional table with a foreign key to this season table, but it add a level of indirection to a database which is now very convoluted. On 29/07/17 17:17, Melvin Davidson wrote: On Sat, Jul 29, 2017 at 3:38 PM, tel medola <mailto:tel.med...@gmail.com>> wrote: Depends. When you create your tables in new schema, the script was the same from "qa"? Sequences, tables, etc.. belong to the schema where was created. Roberto. Em sáb, 29 de jul de 2017 às 16:17, marcelo mailto:marcelo.nico...@gmail.com>> escreveu: Some days ago I asked regarding tables located in different schemas. Now, my question is Suppose I have two schemas (other than public): "qa" and "production". Initially I create all my tables in "qa". All of them have a primary key of type serial. Later, I will copy the tables definitions to production. It will automatically create the sequences in the new schema, starting at zero? TIA Marcelo -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org <mailto:pgsql-general@postgresql.org>) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general <http://www.postgresql.org/mailpref/pgsql-general> *Marcelo, >Initially I create all my tables in "qa". All of them have a primary key of type serial. >Later, I will copy the tables definitions to production. * *A word of caution, creating tables in a qa "schema" and then transferring to production is not the normal/correct (or safe) way to do development. * *The standard procedure is to create a seperate "qa" database (and/or server) with the exact same schema(s) as production. Then, after testing * *is completed, the schemas/tables are copied to production. * -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
Re: [GENERAL] Schemas and serials
Addendum:
Some minutes ago, using EMS SQL Manager Lite, I tried what I was asking.
First, I created a new schema. Then, I duplicated some of the
transactional tables from the public schema, which is acting as a
definition repository for those tables, to the new "transactional"
schema. After that, the serial sequence was created in the test schema.
The only caution is to inspect the sql to be executed, checking to which
schema points every foreign key; the default, obviously, is public. That
is OK when the FK goes to one of the reference tables; but must be
changed when it must go to another transactional one. The example would
be "order" and "order_detail": customer, product, etc must be referenced
from public, but the FK from order_detail must point to season.order.
So, the question is solved, at least using some "postgresql complaint" tool.
Marcelo
On 29/07/17 17:17, Melvin Davidson wrote:
On Sat, Jul 29, 2017 at 3:38 PM, tel medola <mailto:tel.med...@gmail.com>> wrote:
Depends.
When you create your tables in new schema, the script was the same
from "qa"?
Sequences, tables, etc.. belong to the schema where was created.
Roberto.
Em sáb, 29 de jul de 2017 às 16:17, marcelo
mailto:marcelo.nico...@gmail.com>>
escreveu:
Some days ago I asked regarding tables located in different
schemas.
Now, my question is
Suppose I have two schemas (other than public): "qa" and
"production".
Initially I create all my tables in "qa". All of them have a
primary key
of type serial.
Later, I will copy the tables definitions to production.
It will automatically create the sequences in the new schema,
starting
at zero?
TIA
Marcelo
--
Sent via pgsql-general mailing list
(pgsql-general@postgresql.org
<mailto:pgsql-general@postgresql.org>)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
<http://www.postgresql.org/mailpref/pgsql-general>
*Marcelo,
>Initially I create all my tables in "qa". All of them have a primary
key of type serial.
>Later, I will copy the tables definitions to production.
*
*A word of caution, creating tables in a qa "schema" and then
transferring to production is not the normal/correct (or safe) way to
do development.
*
*The standard procedure is to create a seperate "qa" database (and/or
server) with the exact same schema(s) as production. Then, after testing
*
*is completed, the schemas/tables are copied to production.
*
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
[GENERAL] ErrorCode=-2147467259 storing a .net string
The database has UTF8 encoding. The Windows machine's locale where the string was created is set to es_AR.utf8. When sending the data (thru Devart's Devart.Data.PostgreSql module) the server returned the error in the subject. I don't know how exactly the offending string was encoded. Any help will be appreciated. -- 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] ErrorCode=-2147467259 storing a .net string
Solved. I recreated the database with LATIN9 encoding. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] sequence used on null value or get the max value for a column whith concurrency
In some table, I have a bigint column which at the app level can be null. Call it "DocumentNumber", and of course is not the PK. In most cases, the applications give some value to the column. But sometimes, the value remains null, expecting the backend or someone assign it a unique value. Could I use a sequence only when the field arrives to the backend as null? How? Using a triger? Alternatively: How could I get the max value for the column and increment it by one, but with concurrency warranty? Something as a table lock? TIA -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Selecting some schema not suported for libpq PQconnectdbParams
The system I'm building needs to connect some QNX 4.x machines to the Postgres' server (by the way, a Linux machine). Of course, it will be done through a porting of the libpq library. I was reading the possible parameters for the function mentioned in the subject and none refers to the schema. Also, the pg_service.conf file (to which may refer the "service" parameter) don't add to the parameters enumerated in the section 31.1. So... is there another way to tell the server which schema will be the default schema for some database? Of course, I'm asking this because the schema to use will not be the "current user" nor "public". Any help will be appreciated. TIA Marcelo -- 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] Selecting some schema not suported for libpq PQconnectdbParams
Thank you Jerry. I read about "server parameters" as options, but forgotten search_path. On 20/08/17 20:11, Jerry Sievers wrote: marcelo writes: The system I'm building needs to connect some QNX 4.x machines to the Postgres' server (by the way, a Linux machine). Of course, it will be done through a porting of the libpq library. I was reading the possible parameters for the function mentioned in the subject and none refers to the schema. Also, the pg_service.conf file (to which may refer the "service" parameter) don't add to the parameters enumerated in the section 31.1. So... is there another way to tell the server which schema will be the default schema for some database? Of course, I'm asking this because the schema to use will not be the "current user" nor "public". .pg_service.conf... [foo] host=1.2.3.4 port=1234 dbname=groovydb options=-c search_path=your_schema_of_choice,some_more_of_them_maybe HTH Any help will be appreciated. TIA Marcelo -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Porting libpq to QNX 4.25
Is there a libpq porting to QNX 4.25? I just tried to compile one of the modules, but was rejected because the QNX's standard library have not an Int64 type. TIA -- 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] Porting libpq to QNX 4.25
I'm pretty sure that Watcom 9.6 libraries lacks long long or any such variants. And, of course, I don' t have another tool chain. Thank you On 21/08/17 22:20, George Neuner wrote: On Mon, 21 Aug 2017 13:27:56 -0300, marcelo wrote: Is there a libpq porting to QNX 4.25? I just tried to compile one of the modules, but was rejected because the QNX's standard library have not an Int64 type. TIA QNX 4.25 is very old (mid 90's) - its toolchain compiler would be C90 unless you've replaced it with something newer. I'm pretty sure int64_t was not yet a standard type until C99. However, many (most?) compilers already supported 64-bit ints as an extension years before the standard emerged. You might try "__int64", or "long long" (with or without space). Or search the headers for a *_MAX constant equal to 9223372036854775807. [i.e. (2^63)-1] George -- 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] Porting libpq to QNX 4.25
Thank you, Tom. We'll replace those QNX machines with WIndows XP ones (via dual boot), so we can use our Devart's ORM the same as the most "user oriented" applications. On 22/08/17 12:39, Tom Lane wrote: marcelo writes: Is there a libpq porting to QNX 4.25? I just tried to compile one of the modules, but was rejected because the QNX's standard library have not an Int64 type. We removed QNX support in 8.2, so you could try using some pre-8.2 release. It's possible it was broken for awhile before that, though, since the reason for killing it was that no one had shown any interest in testing it in a long time. 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] Porting libpq to QNX 4.25
Hi Peter Do you believe the only path is Windows 10? Those machines are commanding Zebra printers and collecting data in a very harsh environment. So, the cheaper, the better. Why not Linux? Simply because I have Devart's Entity Developer and linqConnect to interface with postgres and I prefer to do all the development using only one paradigm. My best regards Marcelo On 25/08/17 15:26, Peter J. Holzer wrote: On 2017-08-22 12:57:15 -0300, marcelo wrote: We'll replace those QNX machines with WIndows XP ones The future is already here — it's just not very evenly distributed. SCNR, hp -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Call a builtin function from Devart's linqConnect
Sorry I'm asking this question to the list, but the Devart's documentation is very sparse. I would like to call pg_try_advisory_lock( bigint ) and corresponding pg_advisory_unlock( bigint ) from my DAL library. Does someone use this ORM, and call server functions using it? How is it done, and how to get the result? TIA Marcelo -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PG 8.4 and pg_autovacuum functionality
Hello, Since pg_autovacuum no longer exits on PG 8.4 and it seems that one now needs to provide the storage parameters during CREATE TABLE or later on with an ALTER TABLE. Will that ALTER TABLE block anything going on that table until it's finished ? I assume not since no table data is actually being rewritten. Thank you, Marcelo -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] adding columns with defaults is not implemented
Hello, Using Postgres 7.4, I am trying to perform an "alter table temptable add column "myCol" serial" It gives the following msg ERROR: adding columns with defaults is not implemented You cannot add a column that is serial in a table which already has data in postgres 7. Is there a way I can create a serial column on a table which already has data? Or is the only solution upgrading to postgres 8 ? Thanks
Re: [GENERAL] adding columns with defaults is not implemented
Hi, Thanks for your reply, but I have some doubts. Are yoy sugesting I create the column as an Integer then change it to Serial? in Pgsql 7 you cant change a column type. If I create the column as an int then add a default value, how can I make this default value increment with each insert? Thanks again for your help. Marcelo - Original Message - From: "Scott Marlowe" <[EMAIL PROTECTED]> To: "Marcelo" <[EMAIL PROTECTED]> Cc: Sent: Thursday, June 02, 2005 4:43 PM Subject: Re: [GENERAL] adding columns with defaults is not implemented > On Thu, 2005-06-02 at 15:29, Marcelo wrote: > > Hello, > > Using Postgres 7.4, I am trying to perform an "alter table > > temptable add column "myCol" serial" > > > > It gives the following msg > > ERROR: adding columns with defaults is not implemented > > > > You cannot add a column that is serial in a table which already has > > data in postgres 7. > > > > Is there a way I can create a serial column on a table which already > > has data? Or is the only solution upgrading to postgres 8 ? > > You can add a default after you add the column with a separate alter > table statement... > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] help with query...
Folks I am confused , way planer it does not use the partial index? query: SELECT cliente_base.* FROM cliente_base WHERE (cliente_base.inst_class_ = 'Cliente' ) ORDER BY cliente_base.nombre ASC plan: QUERY PLAN --- Sort (cost=821.08..837.04 rows=6387 width=378) (actual time=46.809..53.077 rows=6463 loops=1) Sort Key: nombre -> Seq Scan on cliente_base (cost=0.00..417.39 rows=6387 width=378) (actual time=0.033..19.080 rows=6463 loops=1) Filter: ((inst_class_)::text = 'Cliente'::text) Total runtime: 58.280 ms (5 rows) any help be appreciated pd: sorry for my english. data: table definition: -- Table: cliente_base CREATE TABLE cliente_base ( id_ integer NOT NULL, activo boolean, numero integer, categ character varying(1), nombre character varying(40), renglon_1 character varying(40), renglon_2 character varying(25), renglon_3 character varying(15), pobox character varying(7), pais integer, estado character varying(2), att character varying(15), telefono_1 character varying(15), telefono_2 character varying(15), telex_1 character varying(10), web character varying(254), dominio character varying(30), email character varying(255), telecop character varying(15), tarifa character varying(1), doc_clase character varying(2), doc_nro character varying(8), caja_cod character varying(4), caja_nro character varying(10), fecha date, soc_nro integer, ganancia character varying(11), iva character varying(15), folio character varying(3), libro character varying(2), tomo character varying(2), reg_ind character varying(20), cuit character varying(15), carpeta_sn character varying(1), version_ integer, inst_class_ character varying(128), CONSTRAINT cliente_base_pkey PRIMARY KEY (id_) ) WITHOUT OIDS; ALTER TABLE cliente_base OWNER TO postgres; -- Index: i_cliente -- DROP INDEX i_cliente; CREATE INDEX i_cliente ON cliente_base USING btree (activo); -- Index: ipartialagente -- DROP INDEX ipartialagente; CREATE INDEX ipartialagente ON cliente_base USING btree (numero) WHERE inst_class_::text = 'Agente'::text AND activo = true; -- Index: ipartialcliente -- DROP INDEX ipartialcliente; CREATE INDEX ipartialcliente ON cliente_base USING btree (numero) WHERE inst_class_::text = 'Cliente'::text AND activo = true; -- Index: ixclientebase -- DROP INDEX ixclientebase; CREATE INDEX ixclientebase ON cliente_base USING btree (nombre) WHERE inst_class_::text = 'Agente'::text; -- Index: ixclientebase1 -- DROP INDEX ixclientebase1; CREATE INDEX ixclientebase1 ON cliente_base USING btree (nombre) WHERE inst_class_::text = 'Cliente'::text; -- Index: ixclientebase2 -- DROP INDEX ixclientebase2; CREATE INDEX ixclientebase2 ON cliente_base USING btree (id_) WHERE inst_class_::text = 'Cliente'::text; -- Index: ixmnombre -- DROP INDEX ixmnombre; CREATE INDEX ixmnombre ON cliente_base USING btree (activo, nombre); -- Index: ixmnumero -- DROP INDEX ixmnumero; CREATE INDEX ixmnumero ON cliente_base USING btree (activo, numero); For the record , i made some queries with statistics proppuses select count(*) from cliente_base; count --- 11791 (1 row) select distinct activo , count(*) from cliente_base group by activo ; activo | count +--- f | 310 t | 11481 (2 rows) select distinct cliente_base.inst_class_ , count(*) from cliente_base group by cliente_base.inst_class_ postgres-# ; inst_class_ | count -+--- Agente | 5328 Cliente | 6463 (2 rows) __ Preguntá. Respondé. Descubrí. Todo lo que querías saber, y lo que ni imaginabas, está en Yahoo! Respuestas (Beta). ¡Probalo ya! http://www.yahoo.com.ar/respuestas ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] help with query
Folks I am confused , way planer it does not use the partial index? any help be appreciated pd: sorry for my english. data: table definition: -- Table: cliente_base CREATE TABLE cliente_base ( id_ integer NOT NULL, activo boolean, numero integer, categ character varying(1), nombre character varying(40), renglon_1 character varying(40), renglon_2 character varying(25), renglon_3 character varying(15), pobox character varying(7), pais integer, estado character varying(2), att character varying(15), telefono_1 character varying(15), telefono_2 character varying(15), telex_1 character varying(10), web character varying(254), dominio character varying(30), email character varying(255), telecop character varying(15), tarifa character varying(1), doc_clase character varying(2), doc_nro character varying(8), caja_cod character varying(4), caja_nro character varying(10), fecha date, soc_nro integer, ganancia character varying(11), iva character varying(15), folio character varying(3), libro character varying(2), tomo character varying(2), reg_ind character varying(20), cuit character varying(15), carpeta_sn character varying(1), version_ integer, inst_class_ character varying(128), CONSTRAINT cliente_base_pkey PRIMARY KEY (id_) ) WITHOUT OIDS; ALTER TABLE cliente_base OWNER TO postgres; -- Index: i_cliente -- DROP INDEX i_cliente; CREATE INDEX i_cliente ON cliente_base USING btree (activo); -- Index: ipartialagente -- DROP INDEX ipartialagente; CREATE INDEX ipartialagente ON cliente_base USING btree (numero) WHERE inst_class_::text = 'Agente'::text AND activo = true; -- Index: ipartialcliente -- DROP INDEX ipartialcliente; CREATE INDEX ipartialcliente ON cliente_base USING btree (numero) WHERE inst_class_::text = 'Cliente'::text AND activo = true; -- Index: ixclientebase -- DROP INDEX ixclientebase; CREATE INDEX ixclientebase ON cliente_base USING btree (nombre) WHERE inst_class_::text = 'Agente'::text; -- Index: ixclientebase1 -- DROP INDEX ixclientebase1; CREATE INDEX ixclientebase1 ON cliente_base USING btree (nombre) WHERE inst_class_::text = 'Cliente'::text; -- Index: ixclientebase2 -- DROP INDEX ixclientebase2; CREATE INDEX ixclientebase2 ON cliente_base USING btree (id_) WHERE inst_class_::text = 'Cliente'::text; -- Index: ixmnombre -- DROP INDEX ixmnombre; CREATE INDEX ixmnombre ON cliente_base USING btree (activo, nombre); -- Index: ixmnumero -- DROP INDEX ixmnumero; CREATE INDEX ixmnumero ON cliente_base USING btree (activo, numero); query: SELECT cliente_base.* FROM cliente_base WHERE (cliente_base.inst_class_ = 'Cliente' ) ORDER BY cliente_base.nombre ASC plan: QUERY PLAN --- Sort (cost=821.08..837.04 rows=6387 width=378) (actual time=46.809..53.077 rows=6463 loops=1) Sort Key: nombre -> Seq Scan on cliente_base (cost=0.00..417.39 rows=6387 width=378) (actual time=0.033..19.080 rows=6463 loops=1) Filter: ((inst_class_)::text = 'Cliente'::text) Total runtime: 58.280 ms (5 rows) For the record , i made some queries with statistics proppuses select count(*) from cliente_base; count --- 11791 (1 row) select distinct activo , count(*) from cliente_base group by activo ; activo | count +--- f | 310 t | 11481 (2 rows) select distinct cliente_base.inst_class_ , count(*) from cliente_base group by cliente_base.inst_class_ postgres-# ; inst_class_ | count -+--- Agente | 5328 Cliente | 6463 (2 rows) __ Preguntá. Respondé. Descubrí. Todo lo que querías saber, y lo que ni imaginabas, está en Yahoo! Respuestas (Beta). ¡Probalo ya! http://www.yahoo.com.ar/respuestas ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] initdb
Hi Jhon Diferent distros put file in diferent path try /usr/local/pgsql/initdb bla bla bla or /var/lib/postgres/bin/initdb bla bla bla or locate initdb for locate that file best regards mdc --- John K Masters <[EMAIL PROTECTED]> escribió: > I feel somewhat embarrassed to post this but I can't > get past the first > post with Postgresql. I have installed onto a Debian > testing system, > created a space for the database cluster on > /usr/local/pgsql/data, > changed owner to postgres and changed permissions to > 0700. > > However, when I try `initdb -D > /usr/local/pgsql/data' I get "Command not > found" > > I've googled for this but found nothing useful. > > Regards, John > -- > War is God's way of teaching Americans geography > Ambrose Bierce (1842 - 1914) > > ---(end of > broadcast)--- > TIP 1: if posting/reading through Usenet, please > send an appropriate >subscribe-nomail command to > [EMAIL PROTECTED] so that your >message can get through to the mailing list > cleanly > __ Preguntá. Respondé. Descubrí. Todo lo que querías saber, y lo que ni imaginabas, está en Yahoo! Respuestas (Beta). ¡Probalo ya! http://www.yahoo.com.ar/respuestas ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] help with libpq program
folks i need help with libpq program ,i made on C program for wrapper libpq.dll program , the routine failing is copy from stdin interface. PQputCopyData return 1 (AKA ok) PQputCopyEnd return 1 (AKA ok) but nothing is append to database. tailing log file invalid input syntax for integer: "3hello world 4.5 " CONTEXT: COPY foo, line 1, column a: "3hello world 4.5 " STATEMENT: copy foo from stdin data seems to be correct "3\ hello world \ 4.5\n" "\\.\n" database ( is for one example found at google) create table foo (a int4, b char(16), d float8); copy foo from stdin; "3\ hello world \ 4.5\n" "\\.\n" I'm wrong? what is way to diagnose? any sugestion are welcomed best regards MDC PD: any example are welcomed too. __ Preguntá. Respondé. Descubrí. Todo lo que querías saber, y lo que ni imaginabas, está en Yahoo! Respuestas (Beta). ¡Probalo ya! http://www.yahoo.com.ar/respuestas ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] help with libpq program
DAnn My c code is one layer for wrap libpq.dll functions i'm using function like start with PGresult *PQexec(PGconn *conn, const char *command); command like 'copy foo from stdin '; int PQputCopyData(PGconn *conn, const char *buffer, int nbytes); (many times) int PQputCopyEnd(PGconn *conn, const char *errormsg); I was sucessfull with append CSV delimiter if not specified is tab character (from documentation) The problem was numeric formats, i can't find solution for this but with CSV append was succesfull. Thank for responses Best regards MDC --- Dann Corbit <[EMAIL PROTECTED]> escribió: > Where is your actual copy statement? > What is your field delimiter? > > Why not post the actual C code for your program, if > it is not too long? > > I guess from what you have posted that the delimiter > you supplied does not match the delimiter from your > copy statement. > > > -Original Message- > > From: [EMAIL PROTECTED] > [mailto:pgsql-general- > > [EMAIL PROTECTED] On Behalf Of marcelo Cortez > > Sent: Saturday, June 16, 2007 9:04 PM > > To: pgsql-general@postgresql.org > > Subject: [GENERAL] help with libpq program > > > > > > folks > > > > i need help with libpq program ,i made on C > program > > for > > wrapper libpq.dll program , the routine failing is > > copy from stdin interface. > > > > PQputCopyData return 1 (AKA ok) > > PQputCopyEnd return 1 (AKA ok) > > but nothing is append to database. > > tailing log file > > > > invalid input syntax for integer: "3hello > world > > 4.5 > > " > > CONTEXT: COPY foo, line 1, column a: "3 > hello > > world 4.5 > > " > > STATEMENT: copy foo from stdin > > > > data seems to be correct > > "3\ hello world \ 4.5\n" > > "\\.\n" > > > > database ( is for one example found at google) > > create table foo (a int4, b char(16), d float8); > > copy foo from stdin; > > > > "3\ hello world \ 4.5\n" > > "\\.\n" > > > > I'm wrong? > > > > what is way to diagnose? > > any sugestion are welcomed > > best regards > > > > MDC > > > > PD: any example are welcomed too. > > > > > > > > > > > > > > > > __ > > Preguntá. Respondé. Descubrí. > > Todo lo que querías saber, y lo que ni imaginabas, > > está en Yahoo! Respuestas (Beta). > > ¡Probalo ya! > > http://www.yahoo.com.ar/respuestas > > > > > > ---(end of > broadcast)--- > > TIP 6: explain analyze is your friend > > ---(end of > broadcast)--- > TIP 1: if posting/reading through Usenet, please > send an appropriate >subscribe-nomail command to > [EMAIL PROTECTED] so that your >message can get through to the mailing list > cleanly > __ Preguntá. Respondé. Descubrí. Todo lo que querías saber, y lo que ni imaginabas, está en Yahoo! Respuestas (Beta). ¡Probalo ya! http://www.yahoo.com.ar/respuestas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] PostgreSQL Installer for Windows x64
Magnus , folks The only caveat i found in winxp64 is with psqlODBC my application dont work very well on it into xp64 platform, the work around was change odbc profile in favor of dns file , with dns file my application return to work, ok. BTW i using linux server without problems, but my client aplication layer was the problem :(. best regards MDC --- Magnus Hagander <[EMAIL PROTECTED]> escribió: > On Wed, Jun 20, 2007 at 10:17:10AM +0900, EBIHARA, > Yuichiro wrote: > > Hi, > > > > Can I get a PostgreSQL Installer for Windows > x64(EM64T)? > > That for 32bit Windows is available at > http://www.postgresql.org/ftp/win32/ but I need x64 > native > > version. > > There is no such thing. PostgreSQL 64-bit is > currently only supported on > Unix based platforms. > > The 32-bit version for Windows works just fine on > 64-bit windows, though. > > //Magnus > > ---(end of > broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org/ > __ Preguntá. Respondé. Descubrí. Todo lo que querías saber, y lo que ni imaginabas, está en Yahoo! Respuestas (Beta). ¡Probalo ya! http://www.yahoo.com.ar/respuestas ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] troubble with contrib compile
hi all i downloaded postgres8.2.4 sources , expand and ./configure and install with success. Now i need one module from contrib directory , fuzzystrmatch cd /postgres/contrib/fuzzymatchstr [ ok ] make throws make Makefile:15: ../../src/Makefile.global: No such file or directory Makefile:16: /contrib/contrib-global.mk: No such file or directory make: *** No rule to make target `/contrib/contrib-global.mk'. Stop. i'm wrong? any clue? best regards mdc more data : ( from select version() ). "PostgreSQL 8.2.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.1 (Gentoo 4.1.1-r3)" __ Preguntá. Respondé. Descubrí. Todo lo que querías saber, y lo que ni imaginabas, está en Yahoo! Respuestas (Beta). ¡Probalo ya! http://www.yahoo.com.ar/respuestas ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] troubble with contrib compile
Tom Thanks works fine! best regards mdc --- Tom Lane <[EMAIL PROTECTED]> escribió: > marcelo Cortez <[EMAIL PROTECTED]> writes: > > i downloaded postgres8.2.4 sources , expand and > > ./configure and install with success. > > Now i need one module from contrib directory , > > fuzzystrmatch > > cd /postgres/contrib/fuzzymatchstr [ ok ] > > make > > throws > > > make > > Makefile:15: ../../src/Makefile.global: No such > file > > or directory > > You seem to have removed the results of configure. > As a general rule > it's best to build the contrib modules in the same > tree where you just > built the Postgres core --- they definitely need > configure's outputs > and I think some of them require other files that > get built along the > way. > > If you are trying to match a previously built core > system, be sure to > re-configure with the exact same configure options, > else the contrib > modules may not work. pg_config --configure will > help refresh your > memory if you forgot what you used ... > > regards, tom lane > > ---(end of > broadcast)--- > TIP 6: explain analyze is your friend > __ Preguntá. Respondé. Descubrí. Todo lo que querías saber, y lo que ni imaginabas, está en Yahoo! Respuestas (Beta). ¡Probalo ya! http://www.yahoo.com.ar/respuestas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] tsearch2 on postgres8.2.4
Hi all has anybody created using Gendict generate dictionary in spanish successful ?. __ Preguntá. Respondé. Descubrí. Todo lo que querías saber, y lo que ni imaginabas, está en Yahoo! Respuestas (Beta). ¡Probalo ya! http://www.yahoo.com.ar/respuestas ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] help with tsearch2 stem compile
hi all i'm using postgresql 8.2.4 and install tsearch2 , but i need spanish idiom. following http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/README.gendict and downloading http://snowball.tartarus.org/algorithms/spanish/stemmer.html stem.c and stem.h ./config.sh -n pt -s -p spanish_ISO_8859_1 -v -C'Snowball stemmer for spanish' Dictname: 'pt' Snowball stemmer: yes Has init method: yes Function prefix: spanish_ISO_8859_1 Source files: stem.c Header files: stem.h Object files: stem.o dict_snowball.o Comment: 'Snowball stemmer for spanish' Directory: ../../dict_pt Build directory... ok Build Makefile... ok Build dict_pt.sql.in... ok Copy source and header files... ok Build sub-include header... ok Build Snowball stemmer... ok Build README.pt... ok All is done and cd ../../dict_pt/ make . . . . stem.c: In function 'spanish_ISO_8859_1_close_env': stem.c:1092: error: too many arguments to function 'SN_close_env' make: *** [stem.o] Error 1 any clue? best regards mdc __ Preguntá. Respondé. Descubrí. Todo lo que querías saber, y lo que ni imaginabas, está en Yahoo! Respuestas (Beta). ¡Probalo ya! http://www.yahoo.com.ar/respuestas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Tsearch2 - spanish
Felipe
--- Felipe de Jesús Molina Bravo
<[EMAIL PROTECTED]> escribió:
> Hi
>
> You are rigth, the output of "show lc_ctype;" is C.
>
> Then I did is:
>
> prueba1=# show lc_ctype;
> lc_ctype
> -
> es_MX.ISO8859-1
> (1 row)
>
> and do it
>
> % initdb -D /YOUR/PATH -E LATIN1 --locale
> es_ES.ISO8859-1
>
> (how you do say)
>
> and "createdb -E iso8859-1 prueba1" and finally
> tsearch2
>
> the original problem is resolved
>
> prueba1=# select to_tsvector('espanol','melón');
> to_tsvector
> -
> 'melón':1
> (1 row)
>
>
> but if I change the sentece for it:
>
> prueba1=# select to_tsvector('espanol','melón perro
> mordelón');
> server closed the connection unexpectedly
> This probably means the server terminated
> abnormally
> before or while processing the request.
> The connection to the server was lost. Attempting
> reset: Failed.
> !>
The same thing he same thing happened my to me at
first time with
Tsearch2 - spanish , i think you need
patch snowball with tsearch_snowball_82 file ,
googling
you find instructions how doit .
best regards
mdc
>
>
> ??? lost the connection ... the server is up
> any idea?
>
> The synonym is intentional
>
>
> thanks in advanced
>
>
> El mar, 18-09-2007 a las 21:40 +0400, Teodor Sigaev
> escribió:
> > > LC_CTYPE="POSIX"
> >
> >
> > pls, output of "show lc_ctype;" command. If it's C
> locale then I can identify
> > problem - characters diacritical mark (as ó) is
> not an alpha character, and
> > ispell dictionary will fail. To fix that you
> should run initdb with options:
> > % initdb -D /YOUR/PATH -E LATIN1 --locale
> es_ES.ISO8859-1
> > or
> > % initdb -D /YOUR/PATH -E UTF8 --locale es_ES.UTF8
> >
> > In last case you should also recode all
> dictionary's datafile in utf8 encoding.
> >
> > >>> prueba=# select
> to_tsvector('espanol','melón');
> > >>> ERROR: Affix parse error at 506 line
> > >> and
> > >>> prueba=# select lexize('sp','melón');
> > >>> lexize
> > >>> -
> > >>> {melon}
> > >>> (1 row)
> > sp is a Snowball stemmer, it doesn't require affix
> file, so it works.
> >
> > By the way, why is synonym dictionary paced after
> ispell? is it intentional?
> > Usually, synonym dictionary goes first, then
> ispell and after all of them snowball.
> >
>
> ---(end of
> broadcast)---
> TIP 1: if posting/reading through Usenet, please
> send an appropriate
>subscribe-nomail command to
> [EMAIL PROTECTED] so that your
>message can get through to the mailing list
> cleanly
>
Seguí de cerca a la Selección Argentina de Rugby en el Mundial de Francia
2007.
http://ar.sports.yahoo.com/mundialderugby
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[GENERAL] 8.3 beta FATAL: invalid value for parameter "timezone_abbreviations": "Default"
folks i've installed 8.3beta but at start up receive FATAL: invalid value for parameter "timezone_abbreviations": "Default" any clue? best regards. MDC info: Linux richelet-internet 2.6.21.6 #9 SMP Sun Dec 2 17:52:20 ART 2007 i686 Pentium III (Coppermine) GenuineIntel GNU/Linux gp_config BINDIR = /usr/bin DOCDIR = /usr/doc INCLUDEDIR = /usr/include PKGINCLUDEDIR = /usr/include INCLUDEDIR-SERVER = /usr/include/server LIBDIR = /usr/lib PKGLIBDIR = /usr/lib LOCALEDIR = MANDIR = /usr/man SHAREDIR = /usr/share SYSCONFDIR = /usr/etc PGXS = /usr/lib/pgxs/src/makefiles/pgxs.mk CONFIGURE = CC = gcc CPPFLAGS = -D_GNU_SOURCE CFLAGS = -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing CFLAGS_SL = -fpic LDFLAGS = -Wl,-rpath,'/usr/local/pgsql/lib' LDFLAGS_SL = LIBS = -lpgport -lz -lreadline -lcrypt -ldl -lm VERSION = PostgreSQL 8.3beta3 postgresql.conf # - # PostgreSQL configuration file # - # # This file consists of lines of the form: # # name = value # # (The '=' is optional.) White space may be used. Comments are introduced # with '#' anywhere on a line. The complete list of option names and # allowed values can be found in the PostgreSQL documentation. The # commented-out settings shown in this file represent the default values. # # Please note that re-commenting a setting is NOT sufficient to revert it # to the default value, unless you restart the server. # # Any option can also be given as a command line switch to the server, # e.g., 'postgres -c log_connections=on'. Some options can be changed at # run-time with the 'SET' SQL command. # # This file is read on server startup and when the server receives a # SIGHUP. If you edit the file on a running system, you have to SIGHUP the # server for the changes to take effect, or use "pg_ctl reload". Some # settings, which are marked below, require a server shutdown and restart # to take effect. # # Memory units: kB = kilobytes MB = megabytes GB = gigabytes # Time units:ms = milliseconds s = seconds min = minutes h = hours d = days #--- # FILE LOCATIONS #--- # The default values of these variables are driven from the -D command line # switch or PGDATA environment variable, represented here as ConfigDir. #data_directory = 'ConfigDir' # use data in another directory # (change requires restart) #hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file # (change requires restart) #ident_file = 'ConfigDir/pg_ident.conf' # ident configuration file # (change requires restart) # If external_pid_file is not explicitly set, no extra PID file is written. #external_pid_file = '(none)' # write an extra PID file # (change requires restart) #--- # CONNECTIONS AND AUTHENTICATION #--- # - Connection Settings - listen_addresses = '*' # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost', '*' = all # (change requires restart) #port = 5432# (change requires restart) max_connections = 100 # (change requires restart) # Note: increasing max_connections costs ~400 bytes of shared memory per # connection slot, plus lock space (see max_locks_per_transaction). You # might also need to raise shared_buffers to support more connections. #superuser_reserved_connections = 3 # (change requires restart) #unix_socket_directory = '' # (change requires restart) #unix_socket_group = '' # (change requires restart) #unix_socket_permissions = 0777 # octal # (change requires restart) #bonjour_name = '' # defaults to the computer name # (change requires restart) # - Security & Authentication - #authentication_timeout = 1min # 1s-600s #ssl = off # (change requires restart) #ssl_ciphers = 'ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH' # Allowed SSL ciphers # (change requires restart) #password_encryption = on #db_user_namespace = off # Kerberos and GSSAPI #krb_server_keyfile = ''# (change requires restart) #krb_srvname = 'postgres' # (change requires restart, kerberos only) #krb_server_hostname = '' # empty
Re: [GENERAL] 8.3 beta FATAL: invalid value for parameter "timezone_abbreviations": "Default"
Alvaro ,folks --- Alvaro Herrera <[EMAIL PROTECTED]> escribió: > marcelo Cortez escribió: > > folks > > > > > > i've installed 8.3beta but at start up receive > > > > FATAL: invalid value for parameter > > "timezone_abbreviations": "Default" > > Do you have a file named "Default" on the > share/timezonesets dir? Yes i do. > > I'm wondering if your installation is being > mistakenly trying to use a > different sharedir than it should be. What does > pg_config --sharedir your are right . pg_config --sharedir response --> /usr/share i try link file- :) > say; is it what you expect? (i.e. wherever you > installed the beta) > > -- > Alvaro Herrera > http://www.flickr.com/photos/alvherre/ > Este mail se entrega garantizadamente 100% libre de > sarcasmo. > Tarjeta de crédito Yahoo! de Banco Supervielle. Solicitá tu nueva Tarjeta de crédito. De tu PC directo a tu casa. www.tuprimeratarjeta.com.ar ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] postgres8.3beta encodding problem?
Folks select chr(165); ERROR: requested character too large for encoding: 165 it's one old scrip if not remember wrong works postgres in 8.2.4 any clue? best regars mdc info: select version(). "PostgreSQL 8.3beta3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 (Gentoo 4.1.2 p1.0.1)" show all "add_missing_from";"off" "allow_system_table_mods";"off" "archive_command";"(disabled)" "archive_mode";"off" "archive_timeout";"0" "array_nulls";"on" "authentication_timeout";"1min" "autovacuum";"on" "autovacuum_analyze_scale_factor";"0.1" "autovacuum_analyze_threshold";"50" "autovacuum_freeze_max_age";"2" "autovacuum_max_workers";"3" "autovacuum_naptime";"1min" "autovacuum_vacuum_cost_delay";"20ms" "autovacuum_vacuum_cost_limit";"-1" "autovacuum_vacuum_scale_factor";"0.2" "autovacuum_vacuum_threshold";"50" "backslash_quote";"safe_encoding" "bgwriter_delay";"200ms" "bgwriter_lru_maxpages";"100" "bgwriter_lru_multiplier";"2" "block_size";"8192" "bonjour_name";"" "check_function_bodies";"on" "checkpoint_completion_target";"0.5" "checkpoint_segments";"3" "checkpoint_timeout";"5min" "checkpoint_warning";"30s" "client_encoding";"latin1" "client_min_messages";"notice" "commit_delay";"0" "commit_siblings";"5" "config_file";"/usr/local/pgsql/data/postgresql.conf" "constraint_exclusion";"off" "cpu_index_tuple_cost";"0.005" "cpu_operator_cost";"0.0025" "cpu_tuple_cost";"0.01" "custom_variable_classes";"" "data_directory";"/usr/local/pgsql/data" "DateStyle";"ISO, DMY" "db_user_namespace";"off" "deadlock_timeout";"1s" "debug_assertions";"off" "debug_pretty_print";"off" "debug_print_parse";"off" "debug_print_plan";"off" "debug_print_rewritten";"off" "default_statistics_target";"10" "default_tablespace";"" "default_text_search_config";"pg_catalog.spanish" "default_transaction_isolation";"read committed" "default_transaction_read_only";"off" "default_with_oids";"off" "dynamic_library_path";"$libdir" "effective_cache_size";"128MB" "enable_bitmapscan";"on" "enable_hashagg";"on" "enable_hashjoin";"on" "enable_indexscan";"on" "enable_mergejoin";"on" "enable_nestloop";"on" "enable_seqscan";"on" "enable_sort";"on" "enable_tidscan";"on" "escape_string_warning";"on" "explain_pretty_print";"on" "external_pid_file";"" "extra_float_digits";"0" "from_collapse_limit";"8" "fsync";"on" "full_page_writes";"on" "geqo";"on" "geqo_effort";"5" "geqo_generations";"0" "geqo_pool_size";"0" "geqo_selection_bias";"2" "geqo_threshold";"12" "gin_fuzzy_search_limit";"0" "hba_file";"/usr/local/pgsql/data/pg_hba.conf" "ident_file";"/usr/local/pgsql/data/pg_ident.conf" "ignore_system_indexes";"off" "integer_datetimes";"off" "join_collapse_limit";"8" "krb_caseins_users";"off" "krb_realm";"" "krb_server_hostname";"" "krb_server_keyfile";"" "krb_srvname";"postgres" "lc_collate";"es_AR" "lc_ctype";"es_AR" "lc_messages";"es_AR" "lc_monetary";"es_AR" "lc_numeric";"es_AR" "lc_time";"es_AR" "listen_addresses";"*" "local_preload_libraries";"" "log_autovacuum_min_duration";"-1" "log_checkpoints";"off" "log_connections";"off" "log_destination";"stderr" "log_directory";"pg_log" "log_disconnections";"off" "log_duration";"off" "log_error_verbosity";"default" "log_executor_stats";"off" "log_filename";"postgresql-%Y-%m-%d_%H%M%S.log" "log_hostname";"off" "log_line_prefix";"" "log_lock_waits";"off" "log_min_duration_statement";"-1" "log_min_error_statement";"error" "log_min_messages";"notice" "log_parser_stats";"off" "log_planner_stats";"off" "log_rotation_age";"1d" "log_rotation_size";"10MB" "log_statement";"all" "log_statement_stats";"off" "log_temp_files";"-1" "log_timezone";"America/Buenos_Aires" "log_truncate_on_rotation";"off" "logging_collector";"off" "maintenance_work_mem";"16MB" "max_connections";"100" "max_files_per_process";"1000" "max_fsm_pages";"153600" "max_fsm_relations";"1000" "max_function_args";"100" "max_identifier_length";"63" "max_index_keys";"32" "max_locks_per_transaction";"64" "max_prepared_transactions";"5" "max_stack_depth";"2MB" "password_encryption";"on" "port";"5432" "post_auth_delay";"0" "pre_auth_delay";"0" "random_page_cost";"4" "regex_flavor";"advanced" "search_path";""$user",public" "seq_page_cost";"1" "server_encoding";"LATIN1" "server_version";"8.3beta3" "server_version_num";"80300" "session_replication_role";"origin" "shared_buffers";"24MB" "shared_preload_libraries";"" "silent_mode";"off" "sql_inheritance";"on" "ssl";"off" "standard_conforming_strings";"off" "statement_timeout";"0" "superuser_reserved_connections";"3" "synchronous_commit";"on" "syslog_facility";"LOCAL0" "syslog_ident";"postgres" "tcp_keepalives_count";"9" "tcp_keepalives_idle";"7200" "tcp_keepalives_interval";"75" "temp_buffers";"1024" "temp_tablespaces";"" "TimeZone";"America/Buenos_Aires" "timezone_abbreviations";"Default" "trace_notify";"off" "trace_sort";"off" "track_activities";"on" "track_counts";"on" "transaction_isolation";"read committed" "transaction_read_only";"off" "transform_null_equals";"off" "unix_socket_directory";"" "unix_socket_group
[GENERAL] double free corruption?
Folks i received the follow message from backend ,it's this a bug? best regards and happy new year MDC pd: any clue are welcomed. *** glibc detected *** postgres: postgres richelet 201.235.11.133(2504) SELECT: double free or corruption (!prev): 0x0845d7e8 *** === Backtrace: = /lib/libc.so.6[0xb7e0e930] /lib/libc.so.6(__libc_free+0x89)[0xb7e0ff99] postgres: postgres richelet 201.235.11.133(2504) SELECT[0x82b1c0b] postgres: postgres richelet 201.235.11.133(2504) SELECT(MemoryContextDelete+0x42)[0x82b2152] postgres: postgres richelet 201.235.11.133(2504) SELECT(MemoryContextDeleteChildren+0x28)[0x82b2198] postgres: postgres richelet 201.235.11.133(2504) SELECT(MemoryContextDelete+0x12)[0x82b2122] postgres: postgres richelet 201.235.11.133(2504) SELECT(MemoryContextDeleteChildren+0x28)[0x82b2198] postgres: postgres richelet 201.235.11.133(2504) SELECT(AtAbort_Portals+0x6f)[0x82b281f] postgres: postgres richelet 201.235.11.133(2504) SELECT[0x80adef3] postgres: postgres richelet 201.235.11.133(2504) SELECT(AbortCurrentTransaction+0x25)[0x80ae115] postgres: postgres richelet 201.235.11.133(2504) SELECT(PostgresMain+0x25c6)[0x81f7226] postgres: postgres richelet 201.235.11.133(2504) SELECT[0x81ca226] postgres: postgres richelet 201.235.11.133(2504) SELECT(PostmasterMain+0x81d)[0x81caf0d] postgres: postgres richelet 201.235.11.133(2504) SELECT(main+0x1c7)[0x8182e67] /lib/libc.so.6(__libc_start_main+0xd8)[0xb7dc0838] postgres: postgres richelet 201.235.11.133(2504) SELECT[0x807fa81] === Memory map: 08048000-0836a000 r-xp 03:03 715320 /usr/local/pgsql/bin/postgres 0836a000-08373000 rw-p 00321000 03:03 715320 /usr/local/pgsql/bin/postgres 08373000-0846d000 rw-p 08373000 00:00 0 [heap] b5f0-b5f21000 rw-p b5f0 00:00 0 b5f21000-b600 ---p b5f21000 00:00 0 b60c4000-b60ce000 r-xp 03:03 744303 /usr/lib/gcc/i686-pc-linux-gnu/4.1.2/libgcc_s.so.1 b60ce000-b60cf000 rw-p 9000 03:03 744303 /usr/lib/gcc/i686-pc-linux-gnu/4.1.2/libgcc_s.so.1 b60d7000-b60d9000 r-xp 03:03 708661 /usr/lib/gconv/ISO8859-1.so b60d9000-b60db000 rw-p 1000 03:03 708661 /usr/lib/gconv/ISO8859-1.so b60db000-b60e3000 r-xp 03:03 527740 /usr/local/pgsql/lib/fuzzystrmatch.so b60e3000-b60e4000 rw-p 7000 03:03 527740 /usr/local/pgsql/lib/fuzzystrmatch.so b60e4000-b6146000 rw-p b60e4000 00:00 0 b6146000-b6154000 r-xp 03:03 709308 /lib/libresolv-2.5.so b6154000-b6156000 rw-p d000 03:03 709308 /lib/libresolv-2.5.so b6156000-b6158000 rw-p b6156000 00:00 0 b6158000-b615c000 r-xp 03:03 709745 /lib/libnss_dns-2.5.so b615c000-b615e000 rw-p 3000 03:03 709745 /lib/libnss_dns-2.5.so b615e000-b6166000 r-xp 03:03 708470 /lib/libnss_files-2.5.so b6166000-b6168000 rw-p 7000 03:03 708470 /lib/libnss_files-2.5.so b6169000-b617 r--s 03:03 6427 /usr/lib/gconv/gconv-modules.cache b617-b61a3000 r--p 03:03 8975 /usr/lib/locale/es_AR/LC_CTYPE b61a3000-b61a8000 r--p 03:03 16329 /usr/lib/locale/es_AR/LC_COLLATE b61a8000-b7daa000 rw-s 00:08 114456 /SYSV0052e2c1 (deleted) b7daa000-b7dab000 rw-p b7daa000 00:00 0 b7dab000-b7ecd000 r-xp 03:03 709248 /lib/libc-2.5.so b7ecd000-b7ece000 r--p 00122000 03:03 709248 /lib/libc-2.5.so b7ece000-b7ed rw-p 00123000 03:03 709248 /lib/libc-2.5.so b7ed-b7ed3000 rw-p b7ed 00:00 0 b7ed3000-b7ef6000 r-xp 03:03 709734 /lib/libm-2.5.so b7ef6000-b7ef8000 rw-p 00022000 03:03 709734 /lib/libm-2.5.so b7ef8000-b7efa000 r-xp 03:03 709751 /lib/libdl-2.5.so b7efa000-b7efc000 rw-p 1000 03:03 709751 /lib/libdl-2.5.so b7efc000-b7f01000 r-xp 03:03 709885 /lib/libcrypt-2.5.so b7f01000-b7f03000 rw-p 4000 03:03 709885 /lib/libcrypt-2.5.so b7f03000-b7f2b000 rw-p b7f03000 00:00 0 b7f2d000-b7f2e000 r-xp 03:03 715438 /usr/local/pgsql/lib/utf8_and_iso8859_1.so b7f2e000-b7f2f000 rw-p 03:03 715438 /usr/local/pgsql/lib/utf8_and_iso8859_1.so b7f2f000-b7f3 r--p 03:03 206641 /usr/lib/locale/es_AR/LC_TIME b7f3-b7f31000 r--p 03:03 16760 /usr/lib/locale/es_AR/LC_NUMERIC b7f31000-b7f32000 r--p 03:03 206642 /usr/lib/locale/es_AR/LC_MONETARY b7f32000-b7f33000 r--p 03:03 16336 /usr/lib/locale/es_AR/LC_MESSAGES/SYS_LC_MESSAGES b7f33000-b7f4d000 r-xp 03:03 709923 /lib/ld-2.5.so b7f4d000-b7f4e000 r--p 00019000 03:03 709923 /lib/ld-2.5.so b7f4e000-b7f4f000 rw-p 0001a000 03:03 709923 /lib/ld-2.5.so bfdc4000-bfdda000 rw-p bfdc4000 00:00 0 [stack] e000-f000 r-xp 00:00 0 [vdso] LOG: server process (PID 15558) was terminated by signal 6: Aborted LOG: terminating any other active server processes WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded thi
Re: [GENERAL] double free corruption?
Folks sorry i forgot to mention i'm developing one c external program, may be fault is my code , but surprise to me the message, what bad practice generate this behavior? fail seems to be not to reproducible all times, i'm using beta3 version, it's this important? select version: "PostgreSQL 8.3beta3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 (Gentoo 4.1.2 p1.0.1)" i'ts correct GCC version? best regards. MDC pd: gdb can help? ( for debugging my own code) links about howto debug? --- Tom Lane <[EMAIL PROTECTED]> escribió: > marcelo Cortez <[EMAIL PROTECTED]> writes: > > *** glibc detected *** postgres: postgres richelet > > 201.235.11.133(2504) SELECT: double free or > corruption > > (!prev): 0x0845d7e8 *** > > What PG version is this? Can you provide a > reproducible test case? > > regards, tom lane > > ---(end of > broadcast)--- > TIP 5: don't forget to increase your free space map > settings > Tarjeta de crédito Yahoo! de Banco Supervielle. Solicitá tu nueva Tarjeta de crédito. De tu PC directo a tu casa. www.tuprimeratarjeta.com.ar ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] 8.3beta bug or feature?
folks the follow queries work in postgres 8.2 but in 8.3beta don't work SELECT c.* FROM c WHERE c.numero LIKE '1%'; i think automatic conversion of numeber to text is the problem , in 8.3beta don't work numero field is integer type any ideas? best regards mdc ps:"PostgreSQL 8.3beta3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 (Gentoo 4.1.2 p1.0.1)" ERROR: operator does not exist: integer ~~ unknown LINE 2: c.numero LIKE '1%') ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. ** Error ** ERROR: operator does not exist: integer ~~ unknown Estado SQL:42883 Sugerencias:No operator matches the given name and argument type(s). You might need to add explicit type casts. Caracter: 141 Tarjeta de crédito Yahoo! de Banco Supervielle. Solicitá tu nueva Tarjeta de crédito. De tu PC directo a tu casa. www.tuprimeratarjeta.com.ar ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] 8.3beta bug or feature?
Pavel --- Pavel Stehule <[EMAIL PROTECTED]> escribió: > Hello, > > it isn't bug. You have to cast to string before. > > http://www.postgresql.org/docs/8.3/static/release-8-3.html > E.1.2.1. General Yes you are right, but my queries was generated for one mapper ,explicit cast is not an option ( not manual code here). there any way simulate previous behavior? (automatic conversion), create cast can help? best regards. mdc > > Regards > Pavel Stehule > > > On 10/01/2008, marcelo Cortez > <[EMAIL PROTECTED]> wrote: > > folks > > > > the follow queries work in postgres 8.2 but > > in 8.3beta don't work > > > > > > SELECT c.* FROM c WHERE c.numero LIKE '1%'; > > > > i think automatic conversion of numeber to text > is > > the problem , in 8.3beta don't work > > numero field is integer type > > any ideas? > > > > best regards > > mdc > > > > ps:"PostgreSQL 8.3beta3 on i686-pc-linux-gnu, > compiled > > by GCC gcc (GCC) 4.1.2 (Gentoo 4.1.2 p1.0.1)" > > > > > > ERROR: operator does not exist: integer ~~ > unknown > > LINE 2: c.numero LIKE '1%') > > > > ^ > > HINT: No operator matches the given name and > argument > > type(s). You might need to add explicit type > casts. > > > > ** Error ** > > > > ERROR: operator does not exist: integer ~~ unknown > > Estado SQL:42883 > > Sugerencias:No operator matches the given name and > > argument type(s). You might need to add explicit > type > > casts. > > Caracter: 141 > > > > > > > > > > > > Tarjeta de crédito Yahoo! de Banco > Supervielle. > > Solicitá tu nueva Tarjeta de crédito. De tu PC > directo a tu casa. www.tuprimeratarjeta.com.ar > > > > ---(end of > broadcast)--- > > TIP 4: Have you searched our list archives? > > > >http://archives.postgresql.org/ > > > Los referentes más importantes en compra/ venta de autos se juntaron: Demotores y Yahoo! Ahora comprar o vender tu auto es más fácil. Vistá ar.autos.yahoo.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] 8.3beta bug or feature?
Alvaro --- Alvaro Herrera <[EMAIL PROTECTED]> escribió: > marcelo Cortez escribió: > > Pavel > > > > > > --- Pavel Stehule <[EMAIL PROTECTED]> > escribió: > > > > > Hello, > > > > > > it isn't bug. You have to cast to string before. > > > > > > > > > http://www.postgresql.org/docs/8.3/static/release-8-3.html > > > E.1.2.1. General > > > > Yes you are right, but my queries was generated > for > > one mapper ,explicit cast is not an option ( not > > manual code here). > > there any way simulate previous behavior? > (automatic > > conversion), create cast can help? > > The cast already exist; I think you could change its > context (from > "explicit" to "assignment" IIRC). This is, of > course, not recommended. > > The operation you show is a pretty stupid thing for > a mapper to do > anyway ... I suggest you fix it. yeap i know , but it's third part component,fix it is not an option this time, previous version of my application works fine in 8.2, but i need 8.3 features like fts and others. best regards. mdc > > -- > Alvaro Herrera > http://www.CommandPrompt.com/ > PostgreSQL Replication, Consulting, Custom > Development, 24x7 support > > ---(end of > broadcast)--- > TIP 1: if posting/reading through Usenet, please > send an appropriate >subscribe-nomail command to > [EMAIL PROTECTED] so that your >message can get through to the mailing list > cleanly > Los referentes más importantes en compra/ venta de autos se juntaron: Demotores y Yahoo! Ahora comprar o vender tu auto es más fácil. Vistá ar.autos.yahoo.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] 8.3beta bug or feature?
Alvaro --- Alvaro Herrera <[EMAIL PROTECTED]> escribió: > marcelo Cortez escribió: > > Pavel > > > > > > --- Pavel Stehule <[EMAIL PROTECTED]> > escribió: > > > > > Hello, > > > > > > it isn't bug. You have to cast to string before. > > > > > > > > > http://www.postgresql.org/docs/8.3/static/release-8-3.html > > > E.1.2.1. General > > > > Yes you are right, but my queries was generated > for > > one mapper ,explicit cast is not an option ( not > > manual code here). > > there any way simulate previous behavior? > (automatic > > conversion), create cast can help? > > The cast already exist; I think you could change its > context (from > "explicit" to "assignment" IIRC). This is, of > course, not recommended. I've created cast with assignment from in4 to text but select 23 LIKE '2%' fail. ERROR: operator does not exist: integer ~~ unknown LINE 1: select 23 LIKE '2%' ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. ** Error ** ERROR: operator does not exist: integer ~~ unknown Estado SQL:42883 Sugerencias:No operator matches the given name and argument type(s). You might need to add explicit type casts. Caracter: 12 and i try with select 23 ::int4 LIKE '2%' text added explicit cast thinking in .. 'integer ~~ unknown' unknow word .. confuse to me. nothing is working any ideas? is posible to locate changes ( into sources) to revert behavior to previous 2.8x version? best regards mdc > > The operation you show is a pretty stupid thing for > a mapper to do > anyway ... I suggest you fix it. > > -- > Alvaro Herrera > http://www.CommandPrompt.com/ > PostgreSQL Replication, Consulting, Custom > Development, 24x7 support > > ---(end of > broadcast)--- > TIP 1: if posting/reading through Usenet, please > send an appropriate >subscribe-nomail command to > [EMAIL PROTECTED] so that your >message can get through to the mailing list > cleanly > Tarjeta de crédito Yahoo! de Banco Supervielle. Solicitá tu nueva Tarjeta de crédito. De tu PC directo a tu casa. www.tuprimeratarjeta.com.ar ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] postgres 8.3 rc-1 ispell installation problem
hi folks i've tried to install ispell for CREATE TEXT SEARCH DICTIONARY spanish_ispell ( TEMPLATE = ispell, DictFile = spanish, AffFile = spanish, StopWords = spanish ); sentence, but, one error ocurrs ERROR: syntax error at line 432 of affix file "/usr/local/pgsql/share/tsearch_data/spanish.affix" the offending line is flag *J:# isimo E> -E, 'ISIMO # grand'isimo <-- here 432 E > -E, 'ISIMOS # grande grand'isimos E > -E, 'ISIMA# grande grand'isima E > -E, 'ISIMAS # grande grand'isimas O > -O, 'ISIMO# tonto tont'isimo O > -O, 'ISIMA# tonto tont'isima i think 'I.. word is not correct for ispell, this should be one Í letter but nothing seems to work .. I've tried select convert( 'ÍSIMO', 'SQL_ASCII', 'UTF8') , but don't work any clue? best regards sorry for my english :) Tarjeta de crédito Yahoo! de Banco Supervielle. Solicitá tu nueva Tarjeta de crédito. De tu PC directo a tu casa. www.tuprimeratarjeta.com.ar ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] postgres 8.3 rc-1 ispell installation problem
Teodor i've tried with openoffice.org but whitout success, :) ERROR: invalid byte sequence for encoding "UTF8": 0xe16261 HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, i've tried too with convmv -f iso-8859-1 --notest -t utf-8 es_ES.dic whitout success too. my database encoding is LATIN1 i missing some thing ? best regards mdc > > flag *J:# isimo > >E> -E, 'ISIMO # grand'isimo <-- here > 432 > > E > -E, 'ISIMOS # grande grand'isimos > > E > -E, 'ISIMA# grande grand'isima > > E > -E, 'ISIMAS # grande grand'isimas > > O > -O, 'ISIMO# tonto tont'isimo > > O > -O, 'ISIMA# tonto tont'isima > > > Current implementation doesn't accept any character > in ending except alpha ones. > > > i think 'I.. word is not correct for ispell, > > this should be one Í letter > That's right, but you should convert dictionary and > affix file in UTF8 encoding. > > > -- > Teodor Sigaev > E-mail: [EMAIL PROTECTED] > > WWW: http://www.sigaev.ru/ > > ---(end of > broadcast)--- > TIP 6: explain analyze is your friend > Yahoo! Encuentros. Ahora encontrar pareja es mucho más fácil, probá el nuevo Yahoo! Encuentros http://yahoo.cupidovirtual.com/servlet/NewRegistration ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] postgres 8.3 rc-1 ispell installation problem
Thanks Teodor for quick response,and your work > That's right, but you should convert dictionary and > affix file in UTF8 encoding. how to i can do it? best regards MDC Yahoo! Encuentros. Ahora encontrar pareja es mucho más fácil, probá el nuevo Yahoo! Encuentros http://yahoo.cupidovirtual.com/servlet/NewRegistration ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] postgres 8.3 rc-1 ispell installation problem [RESOLVED]
Thanks Alvaro iconv -f iso-8859-1 -t utf-8 es_ES.dict > es.dict works!! --- Alvaro Herrera <[EMAIL PROTECTED]> escribió: > marcelo Cortez escribió: > > > i've tried too with > > convmv -f iso-8859-1 --notest -t utf-8 > es_ES.dic > > whitout success too. > > convmv only recodes the name of the file -- the > content is not affected. > To recode the file content you need iconv. > > -- > Alvaro Herrera > http://www.CommandPrompt.com/ > The PostgreSQL Company - Command Prompt, Inc. > > ---(end of > broadcast)--- > TIP 1: if posting/reading through Usenet, please > send an appropriate >subscribe-nomail command to > [EMAIL PROTECTED] so that your >message can get through to the mailing list > cleanly > Tarjeta de crédito Yahoo! de Banco Supervielle. Solicitá tu nueva Tarjeta de crédito. De tu PC directo a tu casa. www.tuprimeratarjeta.com.ar ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] can't create index with 'dowcast' row
Louis what if you create one "wrapper" function immutable? some thing like this. CREATE OR REPLACE FUNCTION myextract(timestamp ) RETURNS date AS $BODY$ BEGIN return extract(date from $1) ; END; $BODY$ LANGUAGE 'plpgsql' IMMUTABLE best regards mdc --- Louis-David Mitterrand <[EMAIL PROTECTED]> escribió: > Hi, > > To constraint unique'ness of my visitors to a 24h > periode I tried > created a index including the 'date' part of the > created_on timestamp: > > CREATE UNIQUE INDEX visit_idx ON visit_buffer USING > btree (id_session, > id_story, created_on::date); > > psql:visit_pkey.sql:5: ERROR: syntax error at or > near "::" > LINE 1: ...buffer USING btree (id_session, > id_story, created_on::date); > > and this: > > CREATE UNIQUE INDEX visit_idx ON visit_buffer USING > btree (id_session, id_story, extract(date from > created_on)); > psql:visit_pkey.sql:4: ERROR: functions in index > expression must be marked IMMUTABLE > > How can I achieve what I am trying? > > Thanks, > > ---(end of > broadcast)--- > TIP 6: explain analyze is your friend > Yahoo! Encuentros. Ahora encontrar pareja es mucho más fácil, probá el nuevo Yahoo! Encuentros http://yahoo.cupidovirtual.com/servlet/NewRegistration ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] can't create index with 'dowcast' row
Sorry forgot to mention later try CREATE UNIQUE INDEX visit_idx ON visit_buffer( id_session, id_story ,myextract(created_on)); best regards > Louis > > what if you create one "wrapper" function > immutable? > some thing like this. > > CREATE OR REPLACE FUNCTION myextract(timestamp ) > RETURNS date AS > $BODY$ > BEGIN > return extract(date from $1) ; > END; > $BODY$ > LANGUAGE 'plpgsql' IMMUTABLE > > best regards > mdc > > > > > --- Louis-David Mitterrand > <[EMAIL PROTECTED]> escribió: > > > Hi, > > > > To constraint unique'ness of my visitors to a 24h > > periode I tried > > created a index including the 'date' part of the > > created_on timestamp: > > > > CREATE UNIQUE INDEX visit_idx ON visit_buffer > USING > > btree (id_session, > > id_story, created_on::date); > > > > psql:visit_pkey.sql:5: ERROR: syntax error at or > > near "::" > > LINE 1: ...buffer USING btree (id_session, > > id_story, created_on::date); > > > > and this: > > > > CREATE UNIQUE INDEX visit_idx ON visit_buffer > USING > > btree (id_session, id_story, extract(date from > > created_on)); > > psql:visit_pkey.sql:4: ERROR: functions in index > > expression must be marked IMMUTABLE > > > > How can I achieve what I am trying? > > > > Thanks, > > > > ---(end of > > broadcast)--- > > TIP 6: explain analyze is your friend > > > > > > Yahoo! Encuentros. > > Ahora encontrar pareja es mucho más fácil, probá el > nuevo Yahoo! Encuentros > http://yahoo.cupidovirtual.com/servlet/NewRegistration > Tarjeta de crédito Yahoo! de Banco Supervielle. Solicitá tu nueva Tarjeta de crédito. De tu PC directo a tu casa. www.tuprimeratarjeta.com.ar ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] pg_dump estimation
is there a way to find out / calculate / estimate how big a pg_dump using plain text format for a DB will be ? I have this system with a 7.4 version and a DB that is over 60GB and I know that the admins have never done a vacuum there. The system only has about 20GB of free space so I don't want to take any chances of filling up the disk due to a pg_dump you know. thanks, Marcelo Linux/Solaris System Administrator [EMAIL PROTECTED] http://www.zeroaccess.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Please remove me from the list!
Please, remove me from the list! Marcelo Giovane
[GENERAL] Comando USE
É possível alterar o database corrente para criar um schema nele sem uso do \c do psql? Obrigado MarceloG
[GENERAL] segmentation fault
hello to all I have the following problem, when making massive update of a table, I received the message "segmentation fault" without no other data of the error in log. Some idea of because it gives east message? I am using gentoo postgres 7.4.6 dual processor HP proliant 5 gigabyte of ram 147 gigabytes of hd. 47 free . BTW the column to update is the one of indice gist of FTS and the table has 22 million registries. any clue? best regards MDC ___ 250MB gratis, Antivirus y Antispam Correo Yahoo!, el mejor correo web del mundo http://correo.yahoo.com.ar ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] segmentation fault
Lonni , folks > > You're doing this from within psql or elsewhere? from putty terminal from windows statiom > What logging level default i' guest > are you using? How 'massive' is this update? full , the update statement not have filter clause. > kind of parameters the update statement call fts function. > have you set for this database? next email y send this information. best regards MDC ___ 250MB gratis, Antivirus y Antispam Correo Yahoo!, el mejor correo web del mundo http://correo.yahoo.com.ar ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] segmentation fault
Lonni , folks configuration data: sorry for long response ;) Linux sume 2.6.9-gentoo-r1 #1 SMP Mon Feb 21 10:46:46 Local time zone must be set--see zic i686 Intel(R) Xeon(TM) CPU 2.80GHz GenuineIntel GNU/Linux postgres: "PostgreSQL 7.4.5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.4 20040623 (Gentoo Linux 3.3.4-r1, ssp-3.3.2-2, pie-8.7.6)" postgresql.conf: # - # PostgreSQL configuration file # - # # This file consists of lines of the form: # # name = value # # (The '=' is optional.) White space may be used. Comments are introduced # with '#' anywhere on a line. The complete list of option names and # allowed values can be found in the PostgreSQL documentation. The # commented-out settings shown in this file represent the default values. # # Any option can also be given as a command line switch to the # postmaster, e.g. 'postmaster -c log_connections=on'. Some options # can be changed at run-time with the 'SET' SQL command. # # This file is read on postmaster startup and when the postmaster # receives a SIGHUP. If you edit the file on a running system, you have # to SIGHUP the postmaster for the changes to take effect, or use # "pg_ctl reload". #--- # CONNECTIONS AND AUTHENTICATION #--- # - Connection Settings - #tcpip_socket = false max_connections = 100 # note: increasing max_connections costs about 500 bytes of shared # memory per connection slot, in addition to costs from shared_buffers # and max_locks_per_transaction. #superuser_reserved_connections = 2 #port = 5432 #unix_socket_directory = '' #unix_socket_group = '' #unix_socket_permissions = 0777 # octal #virtual_host = '' # what interface to listen on; defaults to any #rendezvous_name = '' # defaults to the computer name # - Security & Authentication - #authentication_timeout = 60# 1-600, in seconds #ssl = false #password_encryption = true #krb_server_keyfile = '' #db_user_namespace = false #--- # RESOURCE USAGE (except WAL) #--- # - Memory - shared_buffers = 8192 # min 16, at least max_connections*2, 8KB each sort_mem = 8192 # min 64, size in KB #vacuum_mem = 8192 # min 1024, size in KB # - Free Space Map - #max_fsm_pages = 2 # min max_fsm_relations*16, 6 bytes each #max_fsm_relations = 1000 # min 100, ~50 bytes each # - Kernel Resource Usage - #max_files_per_process = 1000 # min 25 #preload_libraries = '' #--- # WRITE AHEAD LOG #--- # - Settings - fsync = false # turns forced synchronization on or off #wal_sync_method = fsync# the default varies across platforms: # fsync, fdatasync, open_sync, or open_datasync wal_buffers = 40# min 4, 8KB each # - Checkpoints - checkpoint_segments = 9 # in logfile segments, min 1, 16MB each #checkpoint_timeout = 300 # range 30-3600, in seconds #checkpoint_warning = 30# 0 is off, in seconds #commit_delay = 0 # range 0-10, in microseconds #commit_siblings = 5# range 1-1000 #--- # QUERY TUNING #--- # - Planner Method Enabling - #enable_hashagg = true #enable_hashjoin = true #enable_indexscan = true #enable_mergejoin = true #enable_nestloop = true #enable_seqscan = true #enable_sort = true #enable_tidscan = true # - Planner Cost Constants - effective_cache_size = 4000 # typically 8KB each #random_page_cost = 4 # units are one sequential page fetch cost #cpu_tuple_cost = 0.01 # (same) #cpu_index_tuple_cost = 0.001 # (same) #cpu_operator_cost = 0.0025 # (same) # - Genetic Query Optimizer - #geqo = true #geqo_threshold = 11 #geqo_effort = 1 #geqo_generations = 0 #geqo_pool_size = 0 # default based on tables in statement, # range 128-1024 #geqo_selection_bias = 2.0 # range 1.5-2.0 # - Other Planner Options - #default_statistics_target = 10 # range 1-1000 #from_collapse_limit = 8 #join_collapse_limit = 8# 1 disables collapsing of explicit JOINs #--- # ERROR REPORTING AND LOGGING #--- # - Syslog - #syslog = 0 # range 0-2; 0=stdout; 1=both; 2=syslog #syslog_facility = 'LOCAL0' #s
Re: [GENERAL] segmentation fault
Lonni , folks > Full meaning all the rows are being updated? What > kind of data is > this that you're updating? the fts engine using one tsvector field indexing with gist index the statement is something like this update from veryLargeTable set field = to_tsvector( coleace(field1) ); any indication to improve the one configuration the DB. thanks for your support. best regards MDC ___ 250MB gratis, Antivirus y Antispam Correo Yahoo!, el mejor correo web del mundo http://correo.yahoo.com.ar ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] postgres db failure
hi folks the postgres fail with follow mwssage pg_dump: dumpClasses(): SQL command failed pg_dump: Error message from server: ERROR: xlog flush request 190/3F08779C is not satisfied --- flushed only to 190/3FD0 CONTEXT: writing block 268606 of relation 17142/17501 any clue? best regards MDc __ Correo Yahoo! Espacio para todos tus mensajes, antivirus y antispam ¡gratis! ¡Abrí tu cuenta ya! - http://correo.yahoo.com.ar ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] LOG: invalid message length
folks the server log message is LOG: invalid message length any clue? best regards mdc pd: where to found the foot print header messages for bki protocol? ___ A tu celular ¿no le falta algo? Usá Yahoo! Messenger y Correo Yahoo! en tu teléfono celular. Más información en http://movil.yahoo.com.ar ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] invalid message length
folks the server log message is LOG: invalid message length any clue? best regards mdc pd: where can i find header format messages for bki protocol? ___ A tu celular ¿no le falta algo? Usá Yahoo! Messenger y Correo Yahoo! en tu teléfono celular. Más información en http://movil.yahoo.com.ar ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] md5 autentication error
Folks I am a little confused, I am trying to implement md5 autentication protocol, as the field is used salt? is hash of md5 algorithm? how build the key? the server log say: 'Password authentication failed for user' is algun example of as it is the protocol? best regards mdc __ Correo Yahoo! Espacio para todos tus mensajes, antivirus y antispam ¡gratis! ¡Abrí tu cuenta ya! - http://correo.yahoo.com.ar ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] how to xml on debian?
hi any pointer's to install xml on debian be appreciated. best regards mdc ___ A tu celular ¿no le falta algo? Usá Yahoo! Messenger y Correo Yahoo! en tu teléfono celular. Más información en http://movil.yahoo.com.ar ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Data type to store latitude and longitude
Hi guys, Is there a data type to store data extracted from a GPS? The only thing I need is store latitude and longitude. Thanks in advance, Regards, Marcelo P Campinas/SP/Brazil ___ Yahoo! Acesso Grátis - Internet rápida e grátis. Instale o discador agora! http://br.acesso.yahoo.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] fts error
hi folks
the follow script fail
select to_tsquery('hello world ');
-> ERROR: syntax error
how to catch this error, any clue?
best regards
mdc
__
Correo Yahoo!
Espacio para todos tus mensajes, antivirus y antispam ¡gratis!
¡Abrí tu cuenta ya! - http://correo.yahoo.com.ar
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[GENERAL] query don't optimize
hi folks the query: SELECT P.ACT_INCORPORADA, P.FECHA_INICIO, P.OBSERVACIONES1 FROM PASEST AS P WHERE P.FECHA_INICIO between '1999-08-22 00:00:00.00'::timestamp AND '1999-12-22 23:59:00.00'::timestamp ORDER BY P.ACT_INCORPORADA make table scan , the planner don't optimize it, the table definition is CREATE TABLE pasest ( act_principal char(24) NOT NULL, fecha_inicio timestamp NOT NULL, act_incorporada char(24) NOT NULL, codigo_incorporado char(1) NOT NULL, fecha_fin timestamp, id_repart_origen int4 NOT NULL, id_repart_destino int4 NOT NULL, fojas numeric(4) NOT NULL, recibo_suelto char(1), ficha_tramite numeric(6), numer_remito int4, id_reparticion_u int4 NOT NULL, observaciones1 varchar(250), observaciones2 varchar(250), cod_permanencia char(2), estado_pase char(1), paq_actua_anterior char(1) NOT NULL DEFAULT ''::bpchar, actua_caratulacion char(1) NOT NULL DEFAULT ''::bpchar, param_01 char(1) NOT NULL DEFAULT ''::bpchar, param_02 char(1) NOT NULL DEFAULT ''::bpchar, param_03 char(1) NOT NULL DEFAULT ''::bpchar, fts_observaciones tsvector, CONSTRAINT pk_pasest PRIMARY KEY (act_principal, fecha_inicio, act_incorporada), CONSTRAINT fk_permanencia FOREIGN KEY (cod_permanencia) REFERENCES permanet (codigo_permanencia) MATCH SIMPLE ON UPDATE RESTRICT ON DELETE RESTRICT, CONSTRAINT fx_caratult FOREIGN KEY (act_principal) REFERENCES caratult (actuacion_car) MATCH SIMPLE ON UPDATE RESTRICT ON DELETE RESTRICT, CONSTRAINT fx_reparticion_destino FOREIGN KEY (id_repart_destino) REFERENCES repartit (id_reparticion) MATCH SIMPLE ON UPDATE RESTRICT ON DELETE RESTRICT, CONSTRAINT fx_reparticion_id_reparticion_u FOREIGN KEY (id_reparticion_u) REFERENCES repartit (id_reparticion) MATCH SIMPLE ON UPDATE RESTRICT ON DELETE RESTRICT, CONSTRAINT fx_reparticion_origen FOREIGN KEY (id_repart_origen) REFERENCES repartit (id_reparticion) MATCH SIMPLE ON UPDATE RESTRICT ON DELETE RESTRICT ) WITHOUT OIDS; ALTER TABLE pasest OWNER TO postgres; and have this index CREATE INDEX ix9_pasest ON pasest USING btree (fecha_inicio); why planner don't optimize it ? any ideas? "PostgreSQL 7.4.3 on i386-pc-linux-gnu, compiled by GCC 2.95.4" Linux serverf 2.4.26-1-686-smp #1 SMP 4 gigabytes hp proliant series TIA best regards MDC __ Correo Yahoo! Espacio para todos tus mensajes, antivirus y antispam ¡gratis! ¡Abrí tu cuenta ya! - http://correo.yahoo.com.ar ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] postgresql.conf value need advice
folks what is preferible value for stats_reset_on_server_start ? what is default value? best regards MDC __ Correo Yahoo! Espacio para todos tus mensajes, antivirus y antispam ¡gratis! ¡Abrí tu cuenta ya! - http://correo.yahoo.com.ar ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] query optimization
hi guys i need advice for query optimization,take too long time. any coments are welcomed :). best MDC SELECT c.actuacion_car AS c_actuacion, c.comentario1 || ' ' || c.comentario2 || ' ' || c.comentario3 AS c_comentario FROM caratult AS c INNER JOIN extractt AS t1 ON (c.id_extracto_car = t1.id_extracto) INNER JOIN repartit AS r1 ON (c.id_reparticion_uc = r1.id_reparticion) INNER JOIN repartit AS r2 ON (t1.id_reparticion_ext = r2.id_reparticion) INNER JOIN repartit AS r3 ON (c.id_reparticion_car = r3.id_reparticion) LEFT JOIN carintet AS i ON (c.actuacion_car = i.actuacion_int) LEFT JOIN repartit AS r5 ON (i.id_reparticion_i = r5.id_reparticion) LEFT JOIN repartit AS r6 ON (i.id_reparticion_s = r6.id_reparticion) LEFT JOIN carcallt AS l ON (c.actuacion_car = l.actuacion_cal) LEFT JOIN callest AS ll9 ON (l.id_calle_cal = ll9.id_calle) LEFT JOIN callest AS ll10 ON (l.id_calle1_cal = ll10.id_calle) LEFT JOIN callest AS ll11 ON (l.id_calle2_cal = ll11.id_calle) LEFT JOIN callest AS ll12 ON (l.id_esquina_cal = ll12.id_calle) LEFT JOIN pasest AS p ON (c.actuacion_car = p.act_principal) LEFT JOIN repartit AS r7 ON (p.id_repart_origen = r7.id_reparticion) LEFT JOIN repartit AS r8 ON (p.id_repart_destino = r8.id_reparticion) LEFT JOIN repartit AS r9 ON (p.id_reparticion_u = r9.id_reparticion) WHERE letra(i.nota_iniciadora) = 'NO' AND anio(i.nota_iniciadora) = '2005' AND numero(i.nota_iniciadora) = '12' AND repart(i.nota_iniciadora) = 'DGRH' LIMIT 101 TABLE DEFINITION CREATE TABLE caratult ( actuacion_car char(24) NOT NULL, id_reparticion_uc int4 NOT NULL, fecha_inicio timestamp NOT NULL, tipo_actuacion char(1) NOT NULL, id_extracto_car int4, act_extramunicipal char(35), observaciones varchar(250), comentario1 varchar(250) NOT NULL, comentario2 varchar(250), comentario3 varchar(250), si_calle char(1) NOT NULL, verdadera char(1) NOT NULL, orden_pago char(10), fac_tipo char(2), fac_anio numeric(4), fac_nro numeric(8), fac_importe numeric(13), anexos varchar(50), recibo_suelto char(1) NOT NULL, id_actuacion_car int4 NOT NULL, id_reparticion_car int4 NOT NULL, id_secuencia_car int4 NOT NULL, fecha_inicio_real date NOT NULL, fts_comentario tsvector, fts_observaciones tsvector, CONSTRAINT pk_caratult PRIMARY KEY (actuacion_car), CONSTRAINT fx_actuacit FOREIGN KEY (id_actuacion_car) REFERENCES actuacit (id_actuacion) MATCH SIMPLE ON UPDATE RESTRICT ON DELETE RESTRICT, CONSTRAINT fx_extracto FOREIGN KEY (id_extracto_car) REFERENCES extractt (id_extracto) MATCH SIMPLE ON UPDATE RESTRICT ON DELETE RESTRICT, CONSTRAINT fx_reparticion_car FOREIGN KEY (id_reparticion_car) REFERENCES repartit (id_reparticion) MATCH SIMPLE ON UPDATE RESTRICT ON DELETE RESTRICT, CONSTRAINT fx_reparticion_uc FOREIGN KEY (id_reparticion_uc) REFERENCES repartit (id_reparticion) MATCH SIMPLE ON UPDATE RESTRICT ON DELETE RESTRICT, CONSTRAINT fx_secuencia1 FOREIGN KEY (id_secuencia_car) REFERENCES secuenct (id_secuencia) MATCH SIMPLE ON UPDATE RESTRICT ON DELETE RESTRICT ) WITHOUT OIDS; ALTER TABLE caratult OWNER TO postgres; GRANT ALL ON TABLE caratult TO postgres WITH GRANT OPTION; GRANT ALL ON TABLE caratult TO GROUP devel; QUERY PLAN RESULT Limit (cost=0.00..31798.82 rows=4 width=457) -> Nested Loop Left Join (cost=0.00..31798.82 rows=4 width=457) -> Nested Loop Left Join (cost=0.00..31774.69 rows=4 width=461) -> Nested Loop Left Join (cost=0.00..31750.61 rows=4 width=465) -> Nested Loop Left Join (cost=0.00..31726.53 rows=4 width=469) -> Nested Loop Left Join (cost=0.00..31558.52 rows=1 width=457) -> Nested Loop Left Join (cost=0.00..31552.53 rows=1 width=461) -> Nested Loop Left Join (cost=0.00..31546.54 rows=1 width=465) -> Nested Loop Left Join (cost=0.00..31540.55 rows=1 width=469) -> Nested Loop Left Join (cost=0.00..31534.56 rows=1 width=473) -> Nested Loop Left Join (cost=0.00..31528.53 rows=1 width=457) -> Nested Loop Left Join (cost=0.00..31522.51 rows=1 width=461) -> Nested Loop (cost=0.00..31516.49 rows=1 width=465) -> Nested Loop (cost=0.00..31510.47 rows=1 width=469) -> Nested Loop (cost=0.00..31504.64 rows=1 width=469) -> Nested Loop (cost=0.00..31498.62 rows=1 width=473)
Re: [GENERAL] query optimization
hi guys , Richad
you are right!, my apologies
profile data:
postgres: "PostgreSQL 7.4.3 on i386-pc-linux-gnu,
compiled by GCC 2.95.4"
HP proliant
processor: Dual Xeon 3.40 Ghz.
HD:250 Gigabytes hard disk storage.
responses:
> Comment 4 - you apply a LIMIT without an ORDER BY.
> Are you sure this is
> what you want?
yes i think is to truncate result size on the client
side , one message is send to user.
Comment 6 - Do you know about the genetic query
> optimiser and thq
> geqo_xxx configuration settings?
question about this ,my posgresl.conf is
# - Genetic Query Optimizer -
#geqo = true
#geqo_threshold = 11
#geqo_effort = 1
#geqo_generations = 0
#geqo_pool_size = 0 # default based on
tables in statement,
# range 128-1024
#geqo_selection_bias = 2.0 # range 1.5-2.0
is 11 join tables the threshold for activate Genetic
Query Optimizer?
> Comment 5 - you don't show any INDEX definitions, so
> it's difficult to
> know whether they are being used.
ok there is too much data sorry.
table repartit 8000 rows aprox.
CREATE TABLE repartit
(
id_reparticion int4 NOT NULL DEFAULT
nextval('reparticiones'::text),
codigo_reparticion char(9) NOT NULL,
codigo_repar_inter char(9),
nombre_reparticion char(60),
vigencia_desde date NOT NULL,
vigencia_hasta date NOT NULL,
id_calle_repar int4 NOT NULL,
numero char(10) NOT NULL,
piso char(10),
oficina char(10),
telefono char(30),
fax char(30),
email char(30),
codigo_estructura numeric(2) NOT NULL,
repart_presentismo char(16),
id_reparticion_ext int4,
proximo_remito numeric(6) NOT NULL DEFAULT 0,
en_red char(1) NOT NULL DEFAULT ''::bpchar,
sector_mesa char(1) NOT NULL DEFAULT ''::bpchar,
CONSTRAINT pk_repartit PRIMARY KEY (id_reparticion),
CONSTRAINT fx_callest FOREIGN KEY (id_calle_repar)
REFERENCES callest (id_calle) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT fx_estructura FOREIGN KEY
(codigo_estructura)
REFERENCES estructt (codigo_estructura) MATCH
SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT ix1_repartit UNIQUE (codigo_reparticion,
codigo_repar_inter)
)
WITHOUT OIDS;
ALTER TABLE repartit OWNER TO postgres;
GRANT ALL ON TABLE repartit TO postgres WITH GRANT
OPTION;
GRANT ALL ON TABLE repartit TO GROUP devel;
table pasest huge one 20 millions of records
cREATE TABLE pasest
(
act_principal char(24) NOT NULL,
fecha_inicio timestamp NOT NULL,
act_incorporada char(24) NOT NULL,
codigo_incorporado char(1) NOT NULL,
fecha_fin timestamp,
id_repart_origen int4 NOT NULL,
id_repart_destino int4 NOT NULL,
fojas numeric(4) NOT NULL,
recibo_suelto char(1),
ficha_tramite numeric(6),
numer_remito int4,
id_reparticion_u int4 NOT NULL,
observaciones1 varchar(250),
observaciones2 varchar(250),
cod_permanencia char(2),
estado_pase char(1),
paq_actua_anterior char(1) NOT NULL DEFAULT
''::bpchar,
actua_caratulacion char(1) NOT NULL DEFAULT
''::bpchar,
param_01 char(1) NOT NULL DEFAULT ''::bpchar,
param_02 char(1) NOT NULL DEFAULT ''::bpchar,
param_03 char(1) NOT NULL DEFAULT ''::bpchar,
fts_observaciones tsvector,
comp_ano int4,
comp_nro int4,
comp_imp float4,
cuerpos_anexos varchar,
orden_pago varchar,
comp_tipo varchar,
CONSTRAINT pk_pasest PRIMARY KEY (act_principal,
fecha_inicio, act_incorporada),
CONSTRAINT fk_permanencia FOREIGN KEY
(cod_permanencia)
REFERENCES permanet (codigo_permanencia) MATCH
SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT fx_caratult FOREIGN KEY (act_principal)
REFERENCES caratult (actuacion_car) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT fx_reparticion_destino FOREIGN KEY
(id_repart_destino)
REFERENCES repartit (id_reparticion) MATCH
SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT fx_reparticion_id_reparticion_u FOREIGN
KEY (id_reparticion_u)
REFERENCES repartit (id_reparticion) MATCH
SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT fx_reparticion_origen FOREIGN KEY
(id_repart_origen)
REFERENCES repartit (id_reparticion) MATCH
SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT
)
WITHOUT OIDS;
ALTER TABLE pasest OWNER TO postgres;
GRANT ALL ON TABLE pasest TO postgres WITH GRANT
OPTION;
GRANT ALL ON TABLE pasest TO GROUP devel;
indexes
"CREATE INDEX ix2_pasest ON pasest USING btree
(act_incorporada)"
"CREATE INDEX ix3_pasest ON pasest USING btree
(id_repart_origen, numer_remito, fecha_inicio)"
"CREATE INDEX ix4_pasest ON pasest USING btree
(id_repart_destino, fecha_fin)"
"CREATE INDEX ix5_pasest ON pasest USING btree
(id_repart_origen, fecha_inicio)"
"CREATE INDEX ix6_pasest ON pasest USING btree
(cod_permanencia)"
"CREATE INDEX ix7_pasest ON pasest USING btree
(id_reparticion_u)"
"CREATE INDEX ix8_pasest ON pasest USING btree
(numer_remito)"
"CREATE INDEX ix9_pasest ON pasest USING btree
(fecha_inicio)"
"CREATE INDEX ix10_
[GENERAL] lock problem
hi all I have a lock problem, one select query freeze my application, the query in question is on complex select , with many join's, but select all of them. The select * from pg_catalog.pg_locks show many rows while the query freeze my aplication. the aplication use ODBC . any ideas?. any help wellcomed best MDC pd: what query show pg_locks ? , have a view ? sorry for my poor english and thanks for your time. __ Correo Yahoo! Espacio para todos tus mensajes, antivirus y antispam ¡gratis! ¡Abrí tu cuenta ya! - http://correo.yahoo.com.ar ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] lock problem
Hi Richard , folks
the query is:
SELECT c.actuacion_car AS c_actuacion,
c.comentario1 || ' ' || c.comentario2 || ' ' ||
c.comentario3 AS c_comentario
FROM caratult AS c INNER JOIN extractt AS t1 ON
(c.id_extracto_car = t1.id_extracto)
INNER JOIN repartit AS r1 ON (c.id_reparticion_uc =
r1.id_reparticion)
INNER JOIN repartit AS r2 ON (t1.id_reparticion_ext =
r2.id_reparticion)
INNER JOIN repartit AS r3 ON (c.id_reparticion_car =
r3.id_reparticion)
LEFT JOIN carcallt AS l ON (c.actuacion_car =
l.actuacion_cal)
LEFT JOIN callest AS ll9 ON (l.id_calle_cal =
ll9.id_calle)
LEFT JOIN callest AS ll10 ON (l.id_calle1_cal =
ll10.id_calle)
LEFT JOIN callest AS ll11 ON (l.id_calle2_cal =
ll11.id_calle)
LEFT JOIN callest AS ll12 ON (l.id_esquina_cal =
ll12.id_calle)
LEFT JOIN pasest AS p ON (c.actuacion_car =
p.act_principal)
LEFT JOIN repartit AS r7 ON (p.id_repart_origen =
r7.id_reparticion)
LEFT JOIN repartit AS r8 ON (p.id_repart_destino =
r8.id_reparticion)
LEFT JOIN repartit AS r9 ON (p.id_reparticion_u =
r9.id_reparticion)
WHERE anio(c.actuacion_car)::integer = '2005'
::integer AND
DATE_PART('year', fecha_inicio_real) ::integer = 2005
AND
upper(repart(c.actuacion_car)) = upper('AGJ') LIMIT
101
without table definition etc, you see are selects
only.
during this query, select * from pg_catalog.pg_locks
show many rows with exclusive locks , and other
activities or user's be freezed,
best regards.
mdc
pd: other information
"PostgreSQL 7.4.3 on i386-pc-linux-gnu, compiled by
GCC 2.95.4"
kernel: Linux serverf 2.4.26-1-686-smp #1 SMP Fri Aug
20 19:39:10 CEST 2004 i686 unknown
Distro: Debian
--- Richard Huxton escribió:
> marcelo Cortez wrote:
> > hi all
> >
> > I have a lock problem, one select query freeze my
> > application, the query in question is on complex
> > select
>
> An ordinary select doesn't take any locks. What is
> the actual query
> causing this?
>
> --
>Richard Huxton
>Archonet Ltd
>
___
1GB gratis, Antivirus y Antispam
Correo Yahoo!, el mejor correo web del mundo
http://correo.yahoo.com.ar
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[GENERAL] postgres 8.x on debian
Hello all, does anyone know, whether there is a Debian Package for postgresql 8.x for stable version? Thanks in advance. MDC __ Correo Yahoo! Espacio para todos tus mensajes, antivirus y antispam ¡gratis! ¡Abrí tu cuenta ya! - http://correo.yahoo.com.ar ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] lock problem
Richard Sorry for the delay. question, the select * from pg_stat_activity thows datid|datname|procpid|usesysid|usename|current_query|query_start 52800|"sume"|30124|1|"postgres"|""|"2005-09-01 13:30:02.921844-03" 52800|"sume"|30125|1|"postgres"|""|"2005-09-01 13:37:21.631802-03" 52800|"sume"|30186|1|"postgres"|"SELECT c.actuacion_car AS c_actuacion, c.comentario1 || ' ' || c.comentario2 || ' ' || c.comentario3 AS c_comentario FROM caratult AS c INNER JOIN extractt AS t1 ON (c.id_extracto_car = t1.id_extracto) INNER JOIN repartit AS r1 ON (c.id_reparticion_uc = r"|"2005-09-01 13:35:45.152586-03" and the select * from pg_locks relation|database|transaction|pid|mode|granted 53046|52800||30186|"AccessShareLock"|t ||159274343|30125|"ExclusiveLock"|t 73744|52800||30186|"AccessShareLock"|t 16759|52800||30125|"AccessShareLock"|t 53094|52800||30186|"AccessShareLock"|t 73770|52800||30186|"AccessShareLock"|t ||159274288|30186|"ExclusiveLock"|t 73824|52800||30186|"AccessShareLock"|t 53054|52800||30186|"AccessShareLock"|t 73726|52800||30186|"AccessShareLock"|t 53074|52800||30186|"AccessShareLock"|t 53049|52800||30186|"AccessShareLock"|t 53127|52800||30186|"AccessShareLock"|t 9567503|52800||30186|"AccessShareLock"|t 74274|52800||30186|"AccessShareLock"|t this queries show locks into 30816 pid or a'im wrong? best regards MDC --- Richard Huxton escribió: > marcelo Cortez wrote: > > Hi Richard , folks > > > > the query is: > > > > SELECT c.actuacion_car AS c_actuacion, > > c.comentario1 || ' ' || c.comentario2 || ' ' || > > c.comentario3 AS c_comentario > > FROM caratult AS c INNER JOIN extractt AS t1 ON > ...etc > > > I'm not seeing anything here that should take locks, > although it's late > where I am. > > > without table definition etc, you see are selects > > only. > > during this query, select * from > pg_catalog.pg_locks > > show many rows with exclusive locks , and other > > activities or user's be freezed, > > Are you *sure* that it's this query? Could you show > some of the rows > from pg_locks along with corresponding rows from > pg_stat_activity? > > -- >Richard Huxton >Archonet Ltd > > ---(end of > broadcast)--- > TIP 5: don't forget to increase your free space map > settings > __ Correo Yahoo! Espacio para todos tus mensajes, antivirus y antispam ¡gratis! ¡Abrí tu cuenta ya! - http://correo.yahoo.com.ar ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] lock problem
--- Richard Huxton escribió: > marcelo Cortez wrote: > > Hi Richard , folks > > > > the query is: > > > > SELECT c.actuacion_car AS c_actuacion, > > c.comentario1 || ' ' || c.comentario2 || ' ' || > > c.comentario3 AS c_comentario > > FROM caratult AS c INNER JOIN extractt AS t1 ON > ...etc > > > I'm not seeing anything here that should take locks, > although it's late > where I am. > > > without table definition etc, you see are selects > > only. > > during this query, select * from > pg_catalog.pg_locks > > show many rows with exclusive locks , and other > > activities or user's be freezed, > > Are you *sure* that it's this query? Could you show > some of the rows > from pg_locks along with corresponding rows from > pg_stat_activity? > > -- >Richard Huxton >Archonet Ltd > > ---(end of > broadcast)--- > TIP 5: don't forget to increase your free space map > settings > __ Correo Yahoo! Espacio para todos tus mensajes, antivirus y antispam ¡gratis! ¡Abrí tu cuenta ya! - http://correo.yahoo.com.ar ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] lock problem
hi Richard ,folks Question: is normal this locks ? which is the reason of this? thanks for your time best regards MDC --- Richard Huxton escribió: > marcelo Cortez wrote: > > Richard > > > > Sorry for a delay > > > > question the select * from pg_stat_activity > > thows > > > > > datid|datname|procpid|usesysid|usename|current_query|query_start > > > 52800|"sume"|30124|1|"postgres"|""|"2005-09-01 > > 13:30:02.921844-03" > > > 52800|"sume"|30125|1|"postgres"|""|"2005-09-01 > > 13:37:21.631802-03" > > 52800|"sume"|30186|1|"postgres"|"SELECT > > c.actuacion_car AS c_actuacion, c.comentario1 || ' > ' > > || c.comentario2 || ' ' || c.comentario3 AS > > c_comentario FROM caratult AS c INNER JOIN > extractt AS > > t1 ON (c.id_extracto_car = t1.id_extracto) INNER > JOIN > > repartit AS r1 ON (c.id_reparticion_uc = > > r"|"2005-09-01 13:35:45.152586-03" > > > > and the select * from pg_locks > > relation|database|transaction|pid|mode|granted > > 53046|52800||30186|"AccessShareLock"|t > > ||159274343|30125|"ExclusiveLock"|t > > 73744|52800||30186|"AccessShareLock"|t > > 16759|52800||30125|"AccessShareLock"|t > > 53094|52800||30186|"AccessShareLock"|t > > 73770|52800||30186|"AccessShareLock"|t > > ||159274288|30186|"ExclusiveLock"|t > > 73824|52800||30186|"AccessShareLock"|t > > 53054|52800||30186|"AccessShareLock"|t > > 73726|52800||30186|"AccessShareLock"|t > > 53074|52800||30186|"AccessShareLock"|t > > 53049|52800||30186|"AccessShareLock"|t > > 53127|52800||30186|"AccessShareLock"|t > > 9567503|52800||30186|"AccessShareLock"|t > > 74274|52800||30186|"AccessShareLock"|t > > > > this queries show locks into 30816 pid or a'im > wrong? > > That's right - pid=30816 is the backend running the > SELECT > c.actuacion_car... query (see the pg_stat_activity > output). > > The only other locks mentioned are for pid=30125, > which I think are > where you're executing "SELECT * FROM pg_locks" - > bit puzzled as to why > the relation/database columns are blank though. > > In short - I can't see anything blocking your query. > What error message > was telling you that locks were causing a problem? > > -- >Richard Huxton >Archonet Ltd > ¡Llamá y ganá! Usá Yahoo! Messenger con Voz y participá del sorteo de un pasaje a cualquier lugar del mundo. Inscribite aquí: http://messenger.yahoo.com/ar/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] psqlodbc don't work on winxp64
hi folks the lastest postgres odbc driver don't work into winxp64 ( AKA Windows Vista) Operative System. The installer fail, i'm try to register dll manually but still fail at add conection time into control panel any ideas best regards mdc __ Correo Yahoo! Espacio para todos tus mensajes, antivirus y antispam ¡gratis! ¡Abrí tu cuenta ya! - http://correo.yahoo.com.ar ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] how to read bytea field
folks help me ,i cant read bytea type field's. how to convert bytea to text or varchar ? when using bytea types? any clue be appreciated best regards mdc __ Preguntá. Respondé. Descubrí. Todo lo que querías saber, y lo que ni imaginabas, está en Yahoo! Respuestas (Beta). ¡Probalo ya! http://www.yahoo.com.ar/respuestas ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] how to read bytea field
Shoaib ,folks Yes i know, but if your define bytea field and store bytea in this field , decode don't work, because decode function has text parameter not bytea ,so how do that to read bytea field to text again? what function convert bytea to text? best regards mdc --- Shoaib Mir <[EMAIL PROTECTED]> escribió: > This might help you: > > select encode(col1,'escape') from tblBytea; > > where col1 is of type bytea... > > - > Shoaib Mir > EnterpriseDB (www.enterprisedb.com) > > On 1/24/07, marcelo Cortez > <[EMAIL PROTECTED]> wrote: > > > > folks > > > > > > help me ,i cant read bytea type field's. > > how to convert bytea to text or varchar ? > > when using bytea types? > > any clue be appreciated > > best regards > > mdc > > > > > > > > > > > > > > > > __ > > Preguntá. Respondé. Descubrí. > > Todo lo que querías saber, y lo que ni imaginabas, > > está en Yahoo! Respuestas (Beta). > > ¡Probalo ya! > > http://www.yahoo.com.ar/respuestas > > > > > > ---(end of > broadcast)--- > > TIP 3: Have you checked our extensive FAQ? > > > >http://www.postgresql.org/docs/faq > > > __ Preguntá. Respondé. Descubrí. Todo lo que querías saber, y lo que ni imaginabas, está en Yahoo! Respuestas (Beta). ¡Probalo ya! http://www.yahoo.com.ar/respuestas ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] how to read bytea field
hi Markus ,folks > Are you sure you tested with a real bytea field? Yeah , i store bytea using encode function , how you say . The field of my table is bytea type , and store real bytea data in this field. My problem is , i can't convert this field to text anymore, not function receiving bytea and return text exist's. I think my mistake was use bytea field. I thinking in turn this field to text and use decode/encode for storage binary data. please correct me if i'm wrong. best regards mdc > > Regards > > Markus > > > ---(end of > broadcast)--- > TIP 5: don't forget to increase your free space map > settings > __ Preguntá. Respondé. Descubrí. Todo lo que querías saber, y lo que ni imaginabas, está en Yahoo! Respuestas (Beta). ¡Probalo ya! http://www.yahoo.com.ar/respuestas ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] how to read bytea field
folks
my table
CREATE TABLE pblfield
(
id_ integer NOT NULL,
value_field bytea,
name character varying(128),
osset integer,
length integer,
version_ integer,
inst_class_ character varying(128),
CONSTRAINT pblfield_pkey PRIMARY KEY (id_)
)
insert into pblfield( id_ , value_field ) values( 1 ,
encode(E'\\000\\001', 'escape') ) ;
insert into pblfield( id_ , value_field ) values( 2 ,
encode(E'\\000\\002', 'escape') ) ;
etc...
now, i want to recover value_field in text form
some thing like..
select id_ , decode(value_field) from pblfield ;
WRONG WRONG ... decode has text parameter ...!
Ok ,next try .
select id_ , decode(value_field ::text ) from
pblfield.
WRONG WRONG ... bytea not cast to string ...!
so, how do that ???
I think solution is:
CREATE TABLE pblfield
(
id_ integer NOT NULL,
value_field text , /* here text field */
name character varying(128),
osset integer,
length integer,
version_ integer,
inst_class_ character varying(128),
CONSTRAINT pblfield_pkey PRIMARY KEY (id_)
)
insert into pblfield( id_ , value_field ) values( 1 ,
encode('\\000\\001', 'escape')::text ) ;
insert into pblfield( id_ , value_field ) values( 2 ,
encode(E'\\000\\002', 'escape') ::text ) ;
select id_ , value_field from pblfield ;
works and
select id_ , decode(value_field ,'escaped' ) from
pblfield ;
works too!!!
folks thanks for your time and responses.
best regards
Last cuestion , when bytea field ( type) is usable?
for storage to external files???
__
Preguntá. Respondé. Descubrí.
Todo lo que querías saber, y lo que ni imaginabas,
está en Yahoo! Respuestas (Beta).
¡Probalo ya!
http://www.yahoo.com.ar/respuestas
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] how to read bytea field
ok my mistake , insert into pblfield( id_ , value_field ) values(1 ,encode(E'\\000\\001', 'escape') ::bytea ) ; best regards mdc __ Preguntá. Respondé. Descubrí. Todo lo que querías saber, y lo que ni imaginabas, está en Yahoo! Respuestas (Beta). ¡Probalo ya! http://www.yahoo.com.ar/respuestas ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] how to read bytea field
Richard H i'm understood now!, sorry for the noise. thanks very, very much best regards mdc --- Richard Huxton escribió: > marcelo Cortez wrote: > > ok my mistake , > > > > insert into pblfield( id_ , value_field ) > >values(1 ,encode(E'\\000\\001', 'escape') > ::bytea > > ) ; > > No. Use "decode" to convert text to bytea, and > "encode" to convert bytea > to text. > > => CREATE TABLE tb (b bytea); > CREATE TABLE > richardh=> INSERT INTO tb VALUES ( > decode(E'\\000\\001\\002','escape') ); > INSERT 0 1 > richardh=> SELECT encode(b,'escape') FROM tb; > encode > -- > \000\x01\x02 > (1 row) > > richardh=> SELECT encode(b,'hex') FROM tb; > encode > > 000102 > (1 row) > > -- >Richard Huxton >Archonet Ltd > __ Preguntá. Respondé. Descubrí. Todo lo que querías saber, y lo que ni imaginabas, está en Yahoo! Respuestas (Beta). ¡Probalo ya! http://www.yahoo.com.ar/respuestas ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] capacity of tables
People In my experience work very well con tables with 172.000.000 of records ( 172 millions). In fact is not too large number of records for postgresql. important aspect of this installation is your .conf file, take care of this, check old email with config subject. Best regards mdc --- Ron Johnson <[EMAIL PROTECTED]> escribió: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 01/24/07 13:06, guillermo arias wrote: > > Hello, i am Guillermo Arias, from Peru. I have a > doubt about > > capacity of tables. I am developing a software for > accountants, > > and my principal problem is about the table for > the vouchers. I > > have to decide to make a table for each year or > only one table > > for all the years. > > > > This table has 11 fields: varchar(10) and 2 > fields: numeric > > (12,2) and is intended to have 900,000 records per > year x 13 > > years = 11'700,000 records > > PostgreSQL will easily handle 12 million rows. > > > What can you suggest me? i do not want the system > to be slow > > using this table. > > Performance (*not* including hardware) is based on: > 1. Well-written queries. > 2. How the indexes match the queries. EXPLAIN > ANALYZE is your >friend!! > 3. The knowledge that it is expensive to insert > into/update/delete >from an index, so create the indexes you need, > but don't go >crazy. > 4. Continual monitoring: production usage patterns > will probably >be different from what you expected. Do not be > surprised if you >have to add or modify indexes "later on". > 5. Using an up-to-date version of PostgreSQL. > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.6 (GNU/Linux) > > iD8DBQFFt7LsS9HxQb37XmcRAo8QAJwLjj26KiJl7gNvt6joKTuo6oGrIwCfWHcz > y9EqHqWygdYKPss3J47TgUc= > =jaMf > -END PGP SIGNATURE- > > ---(end of > broadcast)--- > TIP 6: explain analyze is your friend > __ Preguntá. Respondé. Descubrí. Todo lo que querías saber, y lo que ni imaginabas, está en Yahoo! Respuestas (Beta). ¡Probalo ya! http://www.yahoo.com.ar/respuestas ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] win32 install fail
oblfolks I can't install postgresql on winxp profesional Installer fail ( creating cluster) Installer with out cluster . ok creating cluster on hand .fail initdb The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locale Spanish_Argentina.28605. fixing permissions on existing directory C:/Archivos de programa/PostgreSQL/8.2/data ... ok creating subdirectories ... initdb: could not create directory "C:/Archivos de programa": File exists initdb: removing contents of data directory "C:/Archivos de programa/PostgreSQL/8.2/data" ever error is File exists Any clue? best regards MDC __ Preguntá. Respondé. Descubrí. Todo lo que querías saber, y lo que ni imaginabas, está en Yahoo! Respuestas (Beta). ¡Probalo ya! http://www.yahoo.com.ar/respuestas ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] How to preserve characters with accent?
Hi there --- Peter Eisentraut <[EMAIL PROTECTED]> escribió: > dfx wrote: > > I have some fiel name with character with accent > (à, ò...) but when I > > make a plain backup of schema (file.sql) that > characters are > > converted to strange sequence of two (or more?) > characters. This sounds to me unicode character set .. best regards MDC > > You need to set your server and client encoding > correctly. > > -- > Peter Eisentraut > http://developer.postgresql.org/~petere/ > > ---(end of > broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq > __ Preguntá. Respondé. Descubrí. Todo lo que querías saber, y lo que ni imaginabas, está en Yahoo! Respuestas (Beta). ¡Probalo ya! http://www.yahoo.com.ar/respuestas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Help with install postgres 8.2 win32 version
folks I can't install postgresql on winxp profesional Installer fail ( creating cluster) Installer with out cluster . ok creating cluster on hand .fail initdb The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locale Spanish_Argentina.28605. fixing permissions on existing directory C:/Archivos de programa/PostgreSQL/8.2/data ... ok creating subdirectories ... initdb: could not create directory "C:/Archivos de programa": File exists initdb: removing contents of data directory "C:/Archivos de programa/PostgreSQL/8.2/data" ever error is File exists Any clue? best regards MDC __ Preguntá. Respondé. Descubrí. Todo lo que querías saber, y lo que ni imaginabas, está en Yahoo! Respuestas (Beta). ¡Probalo ya! http://www.yahoo.com.ar/respuestas ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] missing FROM-clause
Hi guys, I upgraded my PostgreSQL server (7.4 to 8.2) and now all my reports refuse to run because the warning "missing FROM-clause". How can I disable it, just to run as the old version?? I have tried: # set add_missing_from to false but, without success!! :( The warning is still there!! Thanks in advance, Marcelo Pereira __ Fale com seus amigos de graça com o novo Yahoo! Messenger http://br.messenger.yahoo.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] missing FROM-clause (more)
Hi guys, (1) I change postgresql.conf: add_missing_from = off (2) pg_ctl stop (3) pg_ctl start Without success!! The warning is still there!! :( Any ideas Thanks in advance, Marcelo Pereira __ Fale com seus amigos de graça com o novo Yahoo! Messenger http://br.messenger.yahoo.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PGSQL 8.2.3 Installation problem
hi there same things occurs to me. Any body install win32 version with success??? best regards MDC --- RPK <[EMAIL PROTECTED]> escribió: > > When I run the setup of PGSQL 8.2.3, it displays > error while initializing > database cluster. Error displayed is: "Failed to > execute initdb. Unable to > set file system permissions". > > I am installing on Windows XP SP2 with > "administrator" log in. > -- > View this message in context: > http://www.nabble.com/PGSQL-8.2.3-Installation-problem-tf3221486.html#a8947083 > Sent from the PostgreSQL - general mailing list > archive at Nabble.com. > > > ---(end of > broadcast)--- > TIP 2: Don't 'kill -9' the postmaster > __ Preguntá. Respondé. Descubrí. Todo lo que querías saber, y lo que ni imaginabas, está en Yahoo! Respuestas (Beta). ¡Probalo ya! http://www.yahoo.com.ar/respuestas ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PGSQL 8.2.3 Installation problem
Magnus I have NTFS only , i don't have FAT partitions at all. But the problem is not resolved. The install fail at create cluster for me. I set all permisions for user postgres. Binary manual installation .. make sense i try . It has any manual/instructions/links for that? Best regards MDC --- Magnus Hagander <[EMAIL PROTECTED]> escribió: > The installer is not supported in a fat environment. > you need to install the binaries manually for that. > > /Magnus > > --- Original message --- > From: RPK <[EMAIL PROTECTED]> > Sent: 2-14-'07, 5:14 > > > > > Paul, > > > > I installed on a Fat32 partition and gave the data > directory path to an NTFS > > partition. I have not set any file permissions. > Installing as a default > > "postgres" user. > > > > > > Paul Lambert-2 wrote: > > > > > > marcelo Cortez wrote: > > >> hi there > > >> > > >> same things occurs to me. > > >> Any body install win32 version with success??? > > >> > > >> best regards > > >> MDC > > >> > > >> > > >> --- RPK <[EMAIL PROTECTED]> > escribió: > > >> > > >>> When I run the setup of PGSQL 8.2.3, it > displays > > >>> error while initializing > > >>> database cluster. Error displayed is: "Failed > to > > >>> execute initdb. Unable to > > >>> set file system permissions". > > >>> > > >>> I am installing on Windows XP SP2 with > > >>> "administrator" log in. > > >>> -- > > >>> View this message in context: > > >>> > > >> > http://www.nabble.com/PGSQL-8.2.3-Installation-problem-tf3221486.html#a8947083 > > >>> Sent from the PostgreSQL - general mailing > list > > >>> archive at Nabble.com. > > >>> > > >>> > > >>> ---(end of > > >>> broadcast)--- > > >>> TIP 2: Don't 'kill -9' the postmaster > > >>> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > > >> > __ > > >> Preguntá. Respondé. Descubrí. > > >> Todo lo que querías saber, y lo que ni > imaginabas, > > >> está en Yahoo! Respuestas (Beta). > > >> ¡Probalo ya! > > >> http://www.yahoo.com.ar/respuestas > > >> > > >> > > >> ---(end of > broadcast)--- > > >> TIP 3: Have you checked our extensive FAQ? > > >> > > >> > http://www.postgresql.org/docs/faq > > >> > > >> > > > > > > I've installed it on my WinXP Professional SP2 > (32 bit) machine without > > > error. > > > > > > Action start 6:33:07: SetPermissions. > > > 1: Setting filesystem permissions... > > > Action ended 6:33:07: SetPermissions. Return > value 1. > > > MSI (s) (F4:44) [06:33:07:312]: Doing action: > RunInitdb > > > Action 6:33:07: RunInitdb. Initializing database > cluster (this may take > > > a minute or two)... > > > Action start 6:33:07: RunInitdb. > > > 1: Initializing database cluster (this may take > a minute or two)... > > > Action ended 6:33:07: RunInitdb. Return value 1. > > > > > > I wasn't installing under administrator, did > this under my own account > > > and had the install create the 'postgres' user > account. > > > > > > Perhaps something wrong with the default file > permissions where you are > > > installing Postgres. I assume Postgres creates > directories that inherit > > > the parent directory permissions. If you have > given the parent > > > restricted access, the 'postgres' user that PG > runs under may not have > > > access to those dirs. > > > > > > Only thing I can think of anyway, if not then I > am not sure why you > > > would be having a problem. > > > > > > Regards, > > > Paul. > > > > > > -- > > > Paul Lambert > > > Database Administrator > > > AutoLedgers > > > > > > ---(end of > broadcast)--- >
Re: [GENERAL] PGSQL 8.2.3 Installation problem
Paul Thanks for your time. the installer log say: fixing permissions on existing directory C:/Archivos de programa/PostgreSQL/8.2/data ... ok creating subdirectories ... initdb: could not create directory "C:/Archivos de programa": File exists initdb: removing contents of data directory "C:/Archivos de programa/PostgreSQL/8.2/data" note path is truncated in message ??? file not exists i delete directory one more time thanks best regardsd mdc --- Paul Lambert <[EMAIL PROTECTED]> escribió: > marcelo Cortez wrote: > > Magnus > > > > I have NTFS only , i don't have FAT partitions > at > > all. > > But the problem is not resolved. > > The install fail at create cluster for me. > > I set all permisions for user postgres. > > > > Binary manual installation .. make sense i try > . > > It has any manual/instructions/links for that? > > > > Best regards > > MDC > > > > > > > > Have you run the setup with the 'write detailed > installation log to > postgresql-8.2.log in the current directory' > checked? > > Can you paste the relevant bits of that log (which > you can find in the > same directory as the installation file) into a > message so more educated > persons can take a look? > > If it's a file system problem, I'd also suggest > going to > http://www.sysinternals.com to their file and disk > utilities, download > ntfilemon and run it to monitor file activity (HINT: > Set the filter to > include only *postgres*, otherwise you will be > flooded with information > relating to file system access from everything else > on your server - and > turn on advanced output) that may show you more > information about what > the error was from the file system point of view. > > -- > Paul Lambert > Database Administrator > AutoLedgers > > __ Preguntá. Respondé. Descubrí. Todo lo que querías saber, y lo que ni imaginabas, está en Yahoo! Respuestas (Beta). ¡Probalo ya! http://www.yahoo.com.ar/respuestas ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] 8.2.3 AutoVacuum not running
Hi folks I Agree with Stephen ,before update to 8.2.3 version i can see "vacuum database xx " tailing the file log, off course log statements i'ts in 'all' . After update don't see anymore, plus pgAdmin reclaim to me for vacuum databases. best regards MDC --- "Schwenker, Stephen" <[EMAIL PROTECTED]> escribió: > It says it's on and I have also turned on all stats > collecting. > > > -Original Message- > From: Alvaro Herrera > [mailto:[EMAIL PROTECTED] > Sent: Monday, April 09, 2007 3:06 PM > To: Schwenker, Stephen > Cc: Tom Lane; pgsql-general@postgresql.org > Subject: Re: [GENERAL] 8.2.3 AutoVacuum not running > > Schwenker, Stephen wrote: > > Hey, > > > > I've also notice one difference between my 8.1 > instance and my 8.2 > > instance. I run a ps and on the 8.1 instance > there is a 'stats buffer > > > process' and in the 8.2 instance there is no > 'stats buffer instance' > > > > Does that give you anymore reasons as to why the > autovacuum is not > working? > > No -- the stats buffer process was removed in 8.2 on > purpose. > > If you do a "show autovacuum", does it show as on? > Maybe it was > disabled due to misconfiguration. > > -- > Alvaro Herrera > http://www.CommandPrompt.com/ > PostgreSQL Replication, Consulting, Custom > Development, 24x7 support > > ---(end of > broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq > __ Preguntá. Respondé. Descubrí. Todo lo que querías saber, y lo que ni imaginabas, está en Yahoo! Respuestas (Beta). ¡Probalo ya! http://www.yahoo.com.ar/respuestas ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] string fields helps
Folks
I need to save data on rtf format but , postgres
refuses to storage for backslah or character's
problem's any idea or vice are welcomed best
regards
mdc
pd: sample off data to save ( from postgres log)
"
UPDATE plbrecord SET documentation =
'{\rtf1\ansi\deff0{\fonttbl{\f0\fnil Tahoma;}}
\viewkind4\uc1\pard\lang1033\f0\fs16
\par }
', version_ = 4 WHERE (plbrecord.id_ = 8 AND
plbrecord.version_ = 3 AND plbrecord.inst_class_ =
'EntityDataWindow')
LOG: statement: COMMIT1
LOG: statement: BEGIN
LOG: statement: UPDATE plbrecord SET documentation =
'{\rtf1\ansi\deff0{\fonttbl{\f0\fnil Tahoma;}}
\viewkind4\uc1\pard\lang1033\f0\fs16 SELECT
"PARAMETROS"."PARAMETRO",
\par
substr(to_char("PARAMETROS"."F_VAL_PARAM",
''/MM/DD''), 1,10),
\par
substr(to_char("PARAMETROS"."F_ANUL_PARAM",''/MM/DD''),
1,10),
\par "PARAMETROS"."VALOR1",
\par "PARAMETROS"."VALOR2"
\par FROM "PARAMETROS"
\par " )
\par
\par }
', version_ = 5 WHERE (plbrecord.id_ = 8 AND
plbrecord.version_ = 4 AND plbrecord.inst_class_ =
'EntityDataWindow')
LOG: statement: COMMIT
LOG: statement: BEGIN
LOG: statement: UPDATE plbrecord SET documentation =
'{\rtf1\ansi\deff0{\fonttbl{\f0\fnil\fcharset0
Tahoma;}{\f1\fnil Tahoma;}}
\viewkind4\uc1\pard\lang1033\f0\fs18 SELECT
"PARAMETROS"."PARAMETRO",
\par
substr(to_char("PARAMETROS"."F_VAL_PARAM",
''/MM/DD''), 1,10),
\par
substr(to_char("PARAMETROS"."F_ANUL_PARAM",''/MM/DD''),
1,10),
\par "PARAMETROS"."VALOR1",
\par "PARAMETROS"."VALOR2"
\par FROM "PARAMETROS"
\par " )
\par \f1\fs16
\par }
"
__
Preguntá. Respondé. Descubrí.
Todo lo que querías saber, y lo que ni imaginabas,
está en Yahoo! Respuestas (Beta).
¡Probalo ya!
http://www.yahoo.com.ar/respuestas
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
