Re: [GENERAL] Locking Tables & Backup Inquiry

Carlos Mennens wrote: >> 0 4 * * * /usr/bin/pg_dumpall>  pg_dumpall.$DATE.sql >> >> that'll run at 4am every day. > When I run the command in my shell (not in Cron), I'm prompted for my > login password. Should I change the permissions in pg_hba.conf and > enable INHERIT grants on my user? Should

Re: [GENERAL] Philosophical question

Andreas wrote: > I asked elsewhere about the best way to store db credentials within a > user-session of a web-app. > > It appeared that it was for everybody but me evident that instead of > heaving a db-role+passwd for every user of an application it was better > to have just 1 set of db-credenti

Re: [GENERAL] What's the best way in Postgres9 to store a bunch of arbitrary boolean values for a row?

Mike Christensen wrote: > I have a database full of recipes, one recipe per row. I need to > store a bunch of arbitrary "flags" for each recipe to mark various > properties such as Gluton-Free, No meat, No Red Meat, No Pork, No > Animals, Quick, Easy, Low Fat, Low Sugar, Low Calorie, Low Sodium an

Re: [GENERAL] odbc_fdw

Florian Schwendener wrote: > I'm stuck again, now with the extension itself. I've tried like > everything I could think of filling the fields in > the example on [1]. My database is named "testdb", my table "testtable" > and the (default) schema is > (I believe) "dbo". Does anybody know the values

Re: [GENERAL] Strange problem with turning WAL archiving on

BK wrote: [server complains that wal_level is not set correctly] >> Did you change the correct postgresql.conf? >> Are there more than one lines for wal_level in the file >> (try "grep wal_level postgresql.conf")? > > I tried greping, there is just one nstance of it and is set on archive. > > Any

Re: [GENERAL] Strange problem with turning WAL archiving on

BK wrote: > I've spent a couple of hours trying some WAL archiving functionality on PostgrSQL 9.1 (running on Mac > OS X). I turned on all the needed options as specified in the documentation: > > wal_level = archive > archive_mode = on > archive_command='test ! -f /Volumes/baza/%f && cp %p /Volum

Re: [GENERAL] DDL & DML Logging doesn't work for calling functions

MURAT KOÇ wrote: > If we set log_statement='all', all of sql statements will be logged and log > file will grow up > immediately (also including unnecessary sql statements). > > We don't want all sql statements to be logged, so we continue logging > settings as my previous sending > (log_stateme

Re: [GENERAL] odbc_fdw

Florian Schwendener wrote: [has problems building odbc_fdw] > Oh, didn't see that! Now it says: > > root@ubuntu:/home/user/Downloads/odbc_fdw-0.1.0# > PATH=/usr/local/pgsql/bin/:$PATH make USE_PGXS=1 > make: Nothing to be done for `all'. > > I remember trying a few options with the make command.

Re: [GENERAL] DDL & DML Logging doesn't work for calling functions

MURAT KOÇ wrote: > Version is PostgreSQL 9.0.4 on x86_64-unknown-linux-gnu, compiled by GCC gcc > (GCC) 4.1.2 20080704 (Red > Hat 4.1.2-51), 64-bit. > > We set logging parameters as below for DDL & DML Logging: > logging_collector = on > log_statement = mod > log_line_prefix = '%t--%d--%u--%h--%a

Re: [GENERAL] odbc_fdw

Florian Schwendener wrote: > Thank you for your help. As I know little about Linux and only a bit > about make files, I really don't know if > I'm doing the right thing. I've typed this: > > root@ubuntu:/home/user/Downloads/odbc_fdw-0.1.0# > PATH=/usr/local/pgsql/bin/:$PATH make USE_PGXS=1 make >

Re: [GENERAL] odbc_fdw

fschwend at hsr.ch wrote: > I built the current PostgreSQL 9.1.1 sources under Ubuntu 11.04 (in a VMware under Win7). > I followed the steps in this guide: > www.thegeekstuff.com/2009/04/linux-postgresql-install-and-configure-from -source > > It seems to work (I can run the server and connect to i

Re: [GENERAL] convert text field to utf8 in sql_ascii database

Andy Colson wrote: > I am in the middle of a process to get all my data into utf8. As its > not all converted yet, my database encoding is SQL_ASCII. > > I am getting external apps fixed up to write utf8 to the database, and > so far so good. But, I ran across some stuff that needs a one time >

Re: [GENERAL] how could duplicate pkey exist in psql?

Yan Chunlu wrote: > recently I have found several tables has exactly the same pkey, here is the definition: > "diggcontent_data_account_pkey" PRIMARY KEY, btree (thing_id, key) > > > the data is like this: > >159292 | funnypics_link_point | 41 > | num >159292 | funnypics_link_poin

Re: [GENERAL] strange behavior, hoping for an explanation

Chris Travers wrote: > I have found recently that tables in certain contexts seem to have a > name pseudocolumn. I was wondering if there is any documentation as > to what this is and what it signifies. > > postgres=# CREATE table TEST2 (a text, b text); > CREATE TABLE > postgres=# INSERT INTO te

Re: [GENERAL] ERROR from pg_restore - From OS X to Ubuntu

Naoko Reeves wrote: > I dumped from: > [...] PostgreSQL 9.0.4 [...] > to: > [...] PostgreSQL 9.1.1 [...] > During the restoration I got the following errors: > > ERROR: could not access file "$libdir/targetinfo": No such file or directory > ERROR: function public.pldbg_get_target_info(text, "c

Re: [GENERAL] PostgreSQL Naming Rules

Robert Buckley wrote: > according to this article > http://www.informit.com/articles/article.aspx?p=409471, the naming of > tables, and fields is restricted to 63 characters and must start with an > underscore or letter. Nothing > is however said about in which character set. > > Am I allowed to

Re: [GENERAL] Help with copy (loading TSV file into table as text)

Allan Kamau wrote: > #COPY a.t(raw_data)FROM '/data/tmp/t.txt' WITH FORMAT text; > > yields "ERROR: syntax error at or near "FORMAT" You'll have to use the syntax as documented: COPY ... FROM ... WITH (FORMAT 'text'); Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@

Re: [GENERAL] dll files missing in postgrsql bin folder in Windows

Kalai R wrote: > I am facing this strange problem where my postgres service couldn't start because of some missing dll > files in installation directory's bin folder. > I copied files from other machine and it start working again. After some time the same problem > appeared again on system reboot.

Re: [GENERAL] Are file system level differential/incremental backups possible?

Bob Hatfield wrote: > Is it possible to do a full file system level backup of the data > directory, say once a week, and differentials or incrementals daily? > > I'm wondering if there are files that would normally be removed that a > restore: Full then diff/inc would not remove and perhaps > corr

Re: [GENERAL] how to save a bytea value into a file?

I wrote: [fanlijing wants to write bytea to file] > A simple >COPY (SELECT byteacol WROM mytab WHERE ...) TO 'filename' (FORMAT binary) > should do the trick. Corrections: a) "binary" must be surrounded by single quotes. b) that won't dump just the binary data - you would have to remove the

Re: [GENERAL] Permission for pg_shadow.

AI Rumman wrote: > I have an application which query on the pg_shadow view. But the user that I was provided by my > hosting server does have permission to do that. > I checked that only superuser can query in pg_shadow view. But hosting service provider will not give > me superuser permission. > I

Re: [GENERAL] how to save a bytea value into a file?

fanlijing wrote: > In Fact, I'm doing a porting project from Oracle 10g to PostgreSQL 9.0.4 > > There is a procedure in Oracle 10g to write a blob value into a file using: [...] > I know PostgreSQL doesn't support procedure, so I want to porting it into a > function use LANGUAGE plpgsql. > So I mu

Re: [GENERAL] how to save a bytea value into a file?

fanlijing wrote: > When I want to save a bytea value into a file, what should I do？ > Is there any function dealing with that in PostgreSQL? (like lo_export() to > deal with the large-object) (# I didn't find any) If you want to save it in a file on the server, you can use the COPY statement.

Re: [GENERAL] function "XXX" already exists with same argument types

Alexander Farber wrote: >>> psql:pref-2011-10-05-a.sql:339: ERROR:  function "pref_update_match" >>> already exists with same argument types >>> ALTER FUNCTION >> Likely someone mistakenly added the functions to template1 of the machine >> you're restoring onto and >> they're getting added to the

Re: [GENERAL] Problem dbi_link with postgresql 9.04

Emanuel Araújo wrote: > The field where the problem occurs is of type float or double, when > extracting data from firebird, it > creates the tables materialized as type text. There is problem with null > values ​​or zero bytes. There > are three records with value "1.5" and are those records tha

Re: [GENERAL] user-interface to upload csv files

Robert Buckley wrote: > I am having problems getting csv files into postgres. Does anyone know if > there is an opensource user- > interface to tackle this? Did you try the COPY command (or \copy in psql)? Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org

Re: [GENERAL] Problem dbi_link with postgresql 9.04

Emanuel Araújo wrote: > found that the problem occurs when the dbi_link makes parsing of a field > float / double to a text > field, because when it creates the tables already created with this type of > data. I do not understand that. > Another thing we see is that the problem is not with null

Re: [GENERAL] stored procedures (packages)

J.V. wrote: > I need to run a series of stored procedures, what is the best way to > organize and run. > > Ideally would like something like Oracle PL/SQL where I can put all > methods in one file and create a main() method > > then just : select main() to have them all run. > > If there is a w

Re: [GENERAL] could not access file "$libdir/pg_buffercache": No such file or directory

Royce Ausburn wrote: > I'm in the process of testing out Postgres 9.0 for production use. I've been using it for development > on my mac, a build from EnterpriseDB. We've just installed a 9.0.5 on an Ubuntu (Ubuntu 10.04.3 LTS) > machine from a backport from lucid. There's an existing 8.4.8 postg

Re: [GENERAL] : PostgreSQL Online Backup

Venkat Balaji wrote: > Our problem is - > > We had mistakenly executed "rsync" on the running PostgreSQL data directory (production) and we did > not run "pg_start_backup()". > > Will this harm production ? can this lead to corruption ? I assume that you used rsync to copy *from* the data direc

Re: [GENERAL] : PostgreSQL Online Backup

Venkat Balaji wrote: > We have had situations where-in "rsync" was executed without executing "pg_start_backup()" on the > production data directory and on the next runs, "pg_start_backup()" has been executed with "rsync". > This was to avoid high IO load on production. We ended up getting unmatche

Re: [GENERAL] changing from postgreSQL 8.3 to 9.0

Malm Paul wrote: > I have a database created on ver 8.3 an have restored the database in a new database in PostgreSQL > 9.0. > In the database i'm using a column storing "bytea". > > When trying to read the database with my java application, I have problems reading from the bytea- > stream. > Inte

Re: [GENERAL] Sending Results From One Function As Input into Another Function

Jeff Adams wrote: > I need to send the results (SETOF RECORDS) from one function into another > function, to produce another result (SETOF RECORDS). I am not quite sure how > to do get this done. The first function filters a large table down a more > manageable dataset. I want to send the results o

Re: [GENERAL] Speed of lo_unlink vs. DELETE on BYTEA

Reuven M. Lerner wrote: > When a record in the main table is deleted, there is a rule (yes a rule -- > not a trigger) in the > referencing table that performs a lo_unlink on the associated object. > I just want to check that my intuition is correct: Wouldn't it be way faster > and more efficien

Re: [GENERAL] limitby without orderby

Rohan Malhotra wrote: > What is difference between > > select * from items order by random() limit 5; > > and > > select * items limit 5; > > my basic requirement is to get random rows from a table, my where clause will > make sure I won't get > same rows in repeated execution of above queri

Re: [GENERAL] why VOLATILE attribute is required?

Rafal Pietrak wrote: > ERROR: ALTER ROLE is not allowed in a non-volatile function > > Why??? See http://www.postgresql.org/docs/8.3/static/xfunc-volatility.html : A STABLE function cannot modify the database [...] Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@po

Re: [GENERAL] Problem dbi_link with postgresql 9.04

Emanuel Araújo wrote: > In one of our applications, we use the dbi_link for communication with a > firebird db, > works very well in version 8.3 we have one of our PostgreSQL server (CentOS > 5.3). > We are doing tests for migration to version 9.4 or 9.1, and the use of tests > dbi_link got the

Re: [GENERAL] looking for a faster way to do that

hamann.w wrote:Gesendet: Mi 2011-09-21 17:59 > I have one large table (about a million entries) with an indexed column > containing codes > like ABC3561A, ABC3563X, 72-451-823 etc. (lots of order numbers from different > manufacturers) > > When I ask for a specific i

Re: [GENERAL] Complex query question

Mike Orr wrote: > I have a complex query question whose answer I think would help me to > understand subselects and aggregates better. I have a table with four > columns of interest: > > id (int primary key), loc_title (varchar null), loc_value (float > null), loc_unit (varchar null) > > I want t

Re: [GENERAL] strange table disk sizes

Rik Bellens wrote: > I have two large tables in a database, one containing original data and > the other one derived from the first table. The first table contains > several columns and indexes, while the second table has less columns and > only one index. Both tables have the same number of rows.

Re: [GENERAL] Backup & Restore a database in PostgreSQL

Siva Palanisamy wrote: > However, I get the same list of errors as below. The weird thing is, it appears to be working fine. I > could not able to comprehend the error list! I don't have any clue about it! > > For your information, I don't think am running using the superuser account! But just seem

Re: [GENERAL] pg_largeobject vs pg_toast_XXXX

bubba postgres wrote: > No takers? > Some background I've changed my TOAST type from EXTENDED to MAIN. > After some changes on my DB I notice that where I used to have a large pg_toast_X table, I > now have a large pg_largeobject table. > Can't find an explanation of the difference between the

Re: [GENERAL] interesting finding on order by behaviour

Samuel Hwang wrote: > I ran the same tests in SQL Server 2008R2, Oracle10 and PostgreSQL > 9.0.4 and found something interesting... > > set up > = > drop table t1 > create table t1 (f1 varchar(100)) > insert into t1 (f1) values ('AbC') > insert into t1 (f1) values ('CdE') > insert into t1 (f1)

Re: [GENERAL] Would it be possible

Adarsh Sharma wrote: > I am using Postgres-8.4.2 on Windows system. > I have 2 databases in my postgres database ( globedatabase (21GB), urldatabase). > > I restore globedatabase from a .sql file on yesterday morning.I insert some new data in that database. > In the evening, by mistake I issued a

Re: [GENERAL] Is there a way to 'unrestrict' drop view?

Thomas Pasch wrote: > well, the reason I'm asking is that this *is* posible in Oracle DB. For > me it looks like that the DB knows that the view is broken. You can't > use it, *but* it is still there (and it will be usable again when the > view query is valid again). True, but Oracle pays a price

Re: [GENERAL] Maximum number of client connection supported by Postgres 8.4.6

Jenish Vyas wrote: [unexpectedly runs out of connections] > Exact Error Message is as follow.. > > [ERROR] Error getting DB connection: The connection attempt failed. > [ERROR] Action commit error: Out of database connections. > > This is the output I am getting form application server, On databa

Re: [GENERAL] Maximum number of client connection supported by Postgres 8.4.6

Jenish Vyaswrote: > please let me know what is the maximum number of concurrent client connection supported by Postgres > 8.4.6 > > max_connections = > > For my database, > > If I am running the test for more then 1000 concurrent active user it is showing me error "running out > of connection"

Re: [GENERAL] Identify release contents

Abraham, Danny wrote: > How can I verify that the bug below is fixed for 9.0.4? > > ( Fix Windows shared-memory allocation code (Tsutomu Yamada, Magnus) This bug led to the often- > reported "could not reattach to shared memory" error message.) > > I can tell for sure, reading the release notes tha

Re: [GENERAL] Insufficient privileges.

Dave Coventry wrote: > I am getting the following error message in my Drupal install. > > PDOException: SQLSTATE[42501]: Insufficient privilege: 7 ERROR: > permission denied for sequence currenttest_id_seq: INSERT INTO > currentTest (score) VALUES (:db_insert_placeholder_0); > > This is a table tha

Re: [GENERAL] ERROR: malformed record literal: "",DETAIL: Missing left parenthesis?

> A question about: ERROR: malformed record literal: "" > DETAIL: Missing left parenthesis. > > Can someone tell me what cause the error? > > > Table z_drop; > Column| Type > -+ > run_date| character varying(128)

Re: [GENERAL] cannot "make USE_PGXS=1" b/c no pgxs.mk exists

Jonathan Brinkman wrote: > Makefile:12: /usr/lib/postgresql/8.4/lib/pgxs/src/makefiles/pgxs.mk: No such > file or directory Maybe you have to install the software package that contains PostgreSQL's development environment. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-genera

Re: [GENERAL] Reusing cached prepared statement slow after 5 executions

Dean Rasheed wrote: >> I can confirm, when I call ps.setPrepareThreshold(1) the query is slow >> immediately, so the plan must be different with the server prepared >> statements. > > You can confirm that from psql by doing > > EXPLAIN ANALYSE SELECT ... ; > > and then > > PREPARE ps( ... ) AS SELE

Re: [GENERAL] Oracle / PostgreSQL comparison...

Stephen Frost wrote: > I love how he finishes with the claim that Oracle "keep their finger on > the pulse of where IT is headed", right after admitting that their > client is actually a huge piece of junk. I guess that was just a typo. Shouldn't it read "[Oracle can] keep their fingers on the thr

Re: [GENERAL] Postgres performance and the Linux scheduler

Simon Windsor wrote: > Can the performance of Postgres be boosted, especially on busy systems, using the none default > DEADLINE Scheduler? I think that mostly depends on your storage. I personally have made one experience where (after weeks of trying everything else) I changed the scheduler fro

Re: [GENERAL] Invalid byte sequence for encoding "UTF8": 0xedbebf

BRUSSER Michael wrote: >>> Is there a way to find the records with the text field containing Unicode bytes "0xedbebf"? >>> Unfortunately this is a very old version 7.3.10 >> >> This should work on 7.3 (according to the documentation): >> SELECT id FROM nlsdata WHERE position('\360\235\204\236'::byt

Re: [GENERAL] Invalid byte sequence for encoding "UTF8": 0xedbebf

BRUSSER Michael wrote: > Is there a way to find the records with the text field containing Unicode bytes "0xedbebf"? > > Unfortunately this is a very old version 7.3.10 This should work on 7.3 (according to the documentation): SELECT id FROM nlsdata WHERE position('\360\235\204\236'::bytea IN val

Re: [GENERAL] duplicate key violate error

AI Rumman wrote: > I got duplicate key violate error in the db log for the following query: > INSERT INTO tab1 ( SELECT '1611576', '1187865' WHERE NOT EXISTS ( SELECT 1 FROM tab1 > WHERE id='1611576' AND id2='1187865')) > > The error occured during production time. > But when I manually exe

Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening

Scott Marlowe wrote: > Then just use pid or something that can uniquely identify the queries > when they're running. I recommend %c in log_line_prefix. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.post

Re: [GENERAL] Password issue

Mahmoud wrote: > I am trying to create a database by passing arguments to createdb.exe > but createdb always asks me about the password although I passed -W 123 > to it. > > How can I override password request? > > PS > This my test for creating the database > createdb.exe -U postgres -W 123 -O ad

Re: [GENERAL] What's eating my space ?

Eric McKeeth wrote: >>> I wander what is taking up my space on disk ... >>> >>> btv=# SELECT pg_size_pretty(pg_database_size('btv_good')); >>> pg_size_pretty >>> >>> 10 GB >>> (1 row) [SELECT total size of all non-system tables] >>> The sum of biggest tables is not even close t

Re: [GENERAL] 500KB PDF saving into large object , what is the table size?

Emi Lu wrote: > A question about large object column type in postgresql8.3. > > A pdf file=500KB. > > If saving into large object column, will the table size be around 500KB? > If larger than 500KB, what could be the proximate size? It should never be noticably bigger than 500K. Yours, Laurenz Al

Re: [GENERAL] question about readonly instances

Szymon Guz wrote: >>> I've got a question about quite a strange configuration. >>> I was asked if we can have one storage, with one data directory where one >>> postgresql >>> instance writes data, and many other instances read those. >>> Is that possible without any replication and copying data?

Re: [GENERAL] Unique Session ID in PGSQL?

Pavel Stehule wrote: >> Is there any function in PGSQL that returns an unique Session >> identifier of the actual session? >> For example a Bigint, or GUID, or etc? >> Can I get the living Session identifiers from PG? > > try > > postgres=# select pg_backend_pid(); > pg_backend_pid > -

Re: [GENERAL] Read Committed transaction with long query

Durumdara wrote: >>> C1.) begin read committed >>> C1.) starting this query >>> C1.) query running >>> C2.) begin read committed >>> C2.) update Lookup set Name = "New2" where ID = 2 >>> C2.) commit >>> C1.) query running >>> C1.) query finished >>> >>> Is it possible to the first joins (before C2

Re: [GENERAL] Read Committed transaction with long query

Durumdara wrote: >Two table: >Main >Lookup > >The query is: >select Main.*, Lookup.Name >left join Lookup on (Main.Type_ID = Lookup.ID) hat's not correct SQL, but I think I understand what you mean. >Lookup: >ID Name >1 Value1 >2 Value 2 >3 Value 3 > >Many records is in Main table (for example 1

Re: [GENERAL] PostgreSQL and XA Distributed Transaction Protocol

Christian Ferrari wrote: >>> Writing a specific stub to wrap-up PostgreSQL is not a too >>> difficult task, but I would be sure I am not re-inventing the wheel. >>> How can I am sure the standard XA interface is not availble? >>> Do you know if there is any document about this matter? > >> I search

Re: Res: [GENERAL] COPY ERROR

paulo matadr wrote: >>> When I try to import big file base.txt( 700MB),I get this: >>> >>> x=# create table arquivo_serasa_marco( varchar(3000)); >>> x=# COPY arquivo_serasa_marco from >>> >>> ERROR: literal newline found in data >>> HINT: Use "\n" to represent newline. >>> CONTEXT: CO

Re: [GENERAL] COPY ERROR

paulo matadr wrote: > When I try to import big file base.txt( 700MB),I get this: > > x=# create table arquivo_serasa_marco( varchar(3000)); > x=# COPY arquivo_serasa_marco from > '/usr/local/pgsql/data/base.txt'; > ERROR: literal newline found in data > HINT: Use "\n" to represent newli

Re: [GENERAL] Concatenate

Frank jansen wrote: > can you help me with this tricky concat i have? > I have a function with an execute statement, one line of it doing an md5 > hash of some concatenated xml paths with values. I cannot get this one > work, postgres is always complaing about some things, like: "functions > and

Re: [GENERAL] Before triggers and usage in partitioned tables

Sergio Ramazzina wrote: > I'm new to postgresql and I need some help to understand the > behaviour of before insert triggers in postgresql. I'm trying the sample > documented in the user manual about implementing table partitions > (http://www.postgresql.org/docs/8.4/interactive/ddl-partitioning.

Re: [GENERAL] pgreplay log file replayer released

Dimitri Fontaine wrote: > > One thing that Tsung, recording > > queries as proxy, will never be able to handle are encrypted connections, > > but I guess that's a minor problem. > > Yes, because you typically run the proxy only to record sessions, in > order to prepare the tsung setup. Another way

Re: [GENERAL] pgreplay log file replayer released

Dimitri Fontaine wrote: > Greg Stark writes: >> Do you have a multi-threaded model that tracks which transactions each >> query belonged to and runs them concurrently like they were in the >> original setup? That's what I've been looking for. > > Tsung does that and has been doing it for… quite s

Re: [GENERAL] AIX postgresql error

Vikram Patil wrote: > Thanks Laurence for Reply. > I can actually connect to server using network. I am just trying to > avoid this warning. Your solution for listen_address will work but I > want to keep it as "*" . Somehow it doesn't complain on any other *nix > Operation Systems. Probably IPv6

Re: [GENERAL] AIX postgresql error

Vikram Patil wrote: > On AIX while starting postgresql server I got this error > . But server starts up without any issues. > > LOG: could not bind IPv6 socket: Address already in use > > HINT: Is another postmaster already running on port 8432? If > not, wait a few seconds and retry. >

Re: [GENERAL] definitions of regexp functions required

Ehsan Haq wrote: >I am using a relatively older version of Postgres and I > need to use the following functions. > regexp_matches(), regexp_split_to_array(), and > regexp_split_to_table() . Can anybody provide me the > definitions of these functions. Do you mean the implementation? That's h

Re: [GENERAL] libpq: compatibility with server versions

Felix wrote: > I am using libpq 8.2.4 (and my own wrapper around it) for a > long time now. Due to some performance penalties I would > like to upgrade to 8.4.x libpq. > > Is it o.k. if I upgraded my libpq to the newer 8.4 > libraries but would still connect to old 8.2 servers? > > Are there any

Re: [GENERAL] Yikes: ERROR: out of memory

Carlo Stonebanks wrote: > Now THIS is a new one for me! I have no idea where to even start. Does > anyone know how to look for the error? Below is the query and what I believe > are the related log entries. > [...] > > SELECT facility_id, street_address, base_zip, COUNT(*) AS > provider_count

Re: [GENERAL] Urgent help needed- alias name in update statement

Venkat wrote: > In postgre, when i am trying to give alias name in update > statement like below - > > - > update mytable x > set x.name = 'asdf' > where x.no = 1 > --- > > > is giving error - mytable is not having col x. > > We

Re: [GENERAL] Entering a character code in a query

John Gage wrote: > I would like to use the following query: > > SELECT english || '\n' || english || '\x2028' || french AS > output FROM vocab_words_translated; > > where \x2028 is the hexadecimal code for a soft carriage return. > > However, this does not work. > > Can anyone help with this p

Re: [GENERAL] Libpq: copy file to bytea column

seiliki wrote: >>> The data types of tableout.c1 and tablein.c1 are both bytea. [...] >>> However, I get the following errors from log when calling >>> libpq functions PQputCopyData() and PQputCopyEnd(). >>> >>> 2010-03-06 20:47:42 CST ERROR: invalid byte sequence for encoding "UTF8": >>> 0x

Re: [GENERAL] ERROR: invalid byte sequence for encoding "UTF8": 0x93 Error

Mary Y Wang wrote: > I got the following error and not sure how to fix it. > "psql:/tmp/030610dumpfile.txt:4369: ERROR: invalid byte sequence for > encoding "UTF8": 0x93 > HINT: This error can also happen if the byte sequence does > not match the encoding expected by the server, which is > con

Re: [GENERAL] kernel version impact on PostgreSQL performance

Greg Smith wrote: > Cyril Scetbon wrote: > > Does anyone know what can be the differences between linux kernels > > 2.6.29 and 2.6.30 that can cause this big difference (TPS x 7 !) > > > http://www.phoronix.com/scan.php?page=article&item=linux_2624_2633&num=2 > > Discussed in detail at > http:/

Re: [GENERAL] Libpq: copy file to bytea column

seiliki wrote: > The data types of tableout.c1 and tablein.c1 are both bytea. > I first export tableout.c1 to a file: > > db1=# COPY (SELECT c1 FROM tableout LIMIT 1) TO '/tmp/t'; > > Then I try to import the file to another table. > > This works without flaw: > > db1=# COPY tablein FROM '/tmp

Re: [GENERAL] ERROR: row is too big: size 8176, maximum size 8160

Scott Marlowe wrote: I am using postgresql-8.3.7 and have recently got this error: org.postgresql.util.PSQLException: ERROR: row is too big: size 8168, maximum size 8160 >>> >>> Please show us your table definition. >>> >>> Wild guess: you have many, many columns, non-text (INT

Re: [GENERAL] ERROR: row is too big: size 8176, maximum size 8160

Andreas Kretschmer wrote: > > I am using postgresql-8.3.7 and have recently got this error: > > > > org.postgresql.util.PSQLException: ERROR: row is too big: size 8168, > > maximum size 8160 > > Please show us your table definition. > > Wild guess: you have many, many columns, non-text (INT or

Re: [GENERAL] Two Versions of PostgreSQL Installed - How to uninstall one particular version

Mary Y Wang wrote: > I got this error: > "-bash-2.05b$ /usr/local/pgsql/bin/pg_ctl start > server starting > -bash-2.05b$ FATAL: database files are incompatible with server > DETAIL: The data directory was initialized by PostgreSQL > version 7.3, which is not compatible with this version 8.3.8."

Re: [GENERAL] Explaining duplicate rows in spite of unique index

I wrote: > We recently found a couple of rows in a production database > that had identical values in the columns constituting the primary key > (The problem surfaced because a pg_dump could not be restored). > > Now I'm looking for explanations how this could happen. > > The rows originate from

Re: [GENERAL]

Shu Ho wrote: > do you clean up the server file by removing them > use > > find $logfile -mtime +$NUMBER_DAYS_TO_KEEP -type f -print > -exec rm -f {} \; > > in postgres ? I do it similarly, but I don't use "-exec", I rather pipe the results of find into something like "xargs rm -f" for be

Re: [GENERAL] Not able to change the owner of function

Jignesh Shah wrote: > could you tell me what could be the issue in below command. I > could see that there is an option for changing OWNER of > function but not sure why it is giving this error. > > techdb=# ALTER FUNCTION test_create() SET OWNER TO masanip; > ERROR: unrecognized configuration

[GENERAL] Explaining duplicate rows in spite of unique index

We recently found a couple of rows in a production database that had identical values in the columns constituting the primary key (The problem surfaced because a pg_dump could not be restored). Now I'm looking for explanations how this could happen. The rows originate from around the time when we

Re: [GENERAL] tsearch2 gives NOTICE: word is too long

AI Rumman wrote: > When I am using the query: > > select length(description), > to_tsvector('default',description) as c from crmentity ; > > Getting error: > > NOTICE: word is too long > > Postgresql 8.1. > > Could anyone please tell me why? Because there is a "word" in the "descriptio

Re: [GENERAL] text search in 8.1

AI Rumman wrote: > I have a plan to upgrade database, but right now I have to > use text search indexing for performance improvement. > > Following is the rpm status of my server: > > [r...@vcrmdev01 ~]# rpm -qa|grep postgres > postgresql-8.1.11-1.el5_1.1 > postgresql-python-8.1.11-1.el5_1.1 >

Re: [GENERAL] What is unsecure postgres languages? How to disable them?

dipti shah wrote: > Could anyone please tell me what is unsecure postgres > languages(like C, pgperl, pgpython??). How to disable them or > restrict them only for super user? I have never heard of "unsecure" languages - what exactly do you mean? If you mean "untrusted" languages like PL/PerlU,

Re: [GENERAL] Questions regarding SET option.

Jignesh Shah wrote: > I have been writing a function with SECURITY DEFINER enabled. > Basically, I am looking for ways to override the users SET > option settings while executing my function to prevent the > permissions breach. For example, to override "SET > search_path", I am setting search p

Re: [GENERAL] Postgres Triggers issue

u235sentinel wrote: > I have a strange problem we noticed the other day with > triggers. We're > running 8.3.3 on Solaris 10 (intel) and have a feed that comes in > regularly to populate a table we're working on. The feed works just > fine inserting rows however the following trigger stops th

Re: [GENERAL] Query to find list of dates between two dates

aravind chandu wrote: > can you please help me with the following query > > I need a query that displays all the dates in between two > dates say i give two dates 12/1/2009 and 12/31/2009 The > result should be like this > > 12/1/2009 > 12/2/2009 > 12/3/2009 > > . > . > . > . > . > 12/31/20

Re: [GENERAL] How to escape apostrophes when apostrophes already used to escape something else

Stefan Schwarzer wrote: > probably not too complicated, but although googling my way through > many pages, I don't find the solution. > > I have a query which uses already an apostrophe to escape something > else: > > $query = "SELECT > * > FROM >

Re: [GENERAL] Attribute a value to a record

Florent THOMAS wrote: > I understood that in the Loop you can change the values of a > variable! Exactly what I needed. > but unfortunately all of this seems to be temporary. > Consequently, the record in the table won't be updated by the > changes we made on the local variable even if it points

Re: [GENERAL] Attribute a value to a record

Florent THOMAS wrote: > I'm currently running on pg8.4 and I have a trigger with a loop : > > FOR ventilation_local IN (SELECT * FROM XXX) LOOP > IF (mytest) THEN > ventilation_local.myfield:=mynewvalue; > END IF; > END LOOP; > > my problem is that the record doen't accept the new

Re: [GENERAL] Rows missing from table despite FK constraint

Konrad Garus wrote: > We use PG 8.3. We use pg_dump and pg_restore overnight to create > copies of main database for reporting etc. One dump/restore runs at 9 > PM, another at 11 PM. > > Today I discovered that the restore at 11 PM failed to recreate a > foreign key constraint, because one row fro

<    3   4   5   6   7   8   9   10   11   12   >