Re: [GENERAL] pgpass file type restrictions

Tom Lane wrote: > On many platforms, it's possible for other users to see the environment > variables of a process. So PGPASSWORD is really quite insecure. As said in https://www.postgresql.org/docs/current/static/libpq-envars.html "PGPASSWORD behaves the same as the password

Re: [GENERAL] pgpass file type restrictions

Desidero wrote: > When attempting to use something like an anonymous pipe for a > passfile, psql throws an error stating that it only accepts plain files So the script doing that has access to the password(s) in clear text. Can't it instead push the password into the PGPASSWORD

Re: [GENERAL] Restore LargeObjects on different server

Durumdara wrote: > The pg_catalog schema is system schema, but it is IN the DB. > > Is this true? So OID is not global (out from DB)? The OID generator is global to the instance, but the unicity checks are local to the tables that use OIDs, including large objects. The case when you

Re: [GENERAL] Perl script is killed by SIGPIPE

Yogesh Sharma wrote: > We have found child script is killed by signal 13 SIGPIPE. When duplicate > key violates error occured, script is killed but not all time. "child script" and this kind of error suggests that a forked process inherits a database connection opened by a parent

Re: [GENERAL] Create Action for psql when NOTIFY Recieved

Jerry Regan wrote: > I think I could justify the effort to ‘script’ psql. I’m not so sure I can > justify the effort to write a standalone program. As a hack around psql, you could have a script that feeds psql with "SELECT 1" from time to time and capture only the notifications output:

Re: [GENERAL] Results interpretation

Igor Korot wrote: > In my case I simply executing: > > SELECT t.table_catalog AS catalog, t.table_schema AS schema, > t.table_name AS table, u.usename AS owner, c.oid AS table_id FROM > information_schema.tables t, pg_catalog.pg_class c, pg_catalog.pg_user > u WHERE t.table_name =

Re: [GENERAL] Results interpretation

Igor Korot wrote: > If I do PQexec() call, the results will be interpreted as binary or text? > > I'm trying to get an int field from the query and wonder if I need to do > hton() call or not? In the most general case, you may call PQfformat(const PGresult *res, int column_number) to

Re: [GENERAL] Invalid field size

Tom Lane wrote: > Moreno Andreo writes: > > So the hint is to abandon manual COPY and let pg_dump do the hard work? > > If it is a newline-conversion problem, compressed pg_dump archives would > be just as subject to corruption as your binary COPY file is.

Re: [GENERAL] Invalid field size

Moreno Andreo wrote: > So if it's the case (hardware error), recalling a new backup should > reproduce the error, right? If the error happened when writing the file, I wouldn't expect any other backup having the same error (assuming an error in the bit-flip category). And if it was a

Re: [GENERAL] Invalid field size

Moreno Andreo wrote: > As you can see I have 2 bytea fields, blob and thumbnail (the one it > seems it's giving the error), but AFAIK the former is never used, so it > should be always null. > Googling around did not help. In COPY BINARY, NULL is represented as -1 (all bits set) in the

Re: [GENERAL] Suddenly - LOG: could not open file "postmaster.pid": No such file or directory

Muhammad Hanif Abdul Hamid wrote: > Maybe an irrelevant question, but I would like to know if there anything > from postgres process (e.g any parameter that is not set right) that might > remove the postmaster.pid? If using Linux, you may put it under watch with # auditctl -w

Re: [GENERAL] storing large files in database - performance

Eric Hill wrote: > I am storing the file contents is of type "bytea" with "Storage" type set to > "EXTENDED". Storing a 12.5 MB file is taking 10 seconds That seems really slow indeed. Can you import the same file to the same server with psql's \lo_import command and see how much time

Re: [GENERAL] Unable to connect to Postgresql

John Iliffe wrote: > Yes, I will do that, but there are several hundred PHP web page scripts to > be updated. Presumably if one script opens two different databases then > both of the pg_connect() instances will need to be updated. If you have many calls to pg_connect() without a host

Re: [GENERAL] Unable to connect to Postgresql

John Iliffe wrote: > So, the problem is resolved, although I have no idea why it was necessary. The key seems to be the PrivateTmp=true in the systemd service. Apache is not chrooted as demonstrated upthread, but that setting alone makes the normal, system-wide /tmp inaccessible to its

Re: [GENERAL] Unable to connect to Postgresql

John Iliffe wrote: > Based on the reference that Joe sent earlier, I do have a second domain > socket on /var/pgsql but the problem is how do I get PHP to look there? pg_connect("host=/var/pgsql [...other parameters...]") The fact that the value for host starts with a slash

Re: [GENERAL] Unable to connect to Postgresql

John Iliffe wrote: > > > > > Basically, nothing. > > If I include it in an Apache script exactly as suggested, then the script > puts out a blank screen, no error messages. So from the point of view of the Apache process, that file does not exist. That's consistent with the error

Re: [GENERAL] Unable to connect to Postgresql

John Iliffe wrote: > The proper socket does exist: > > - > ls -al /tmp | grep PGSQL > srwxrwxrwx. 1 postgres postgres0 Apr 7 16:53 .s.PGSQL.5432 > -rw---. 1 postgres postgres 49 Apr 7 16:53 .s.PGSQL.5432.lock Maybe the httpd service

Re: [GENERAL] A change in the Debian install

rob stone wrote: > You create a new cluster by running initdb:- > > initdb -D /path/to/my/new/cluster No, if you want to use the infrastructure brought by the debian packages for your cluster, it should be created with: pg_createcluster 9.5 nameofcluster See

Re: [HACKERS] [GENERAL] Select works only when connected from login postgres

Tom Lane wrote: > BTW, I realized while testing this that there's still one gap in our > understanding of what went wrong for you: cases like "SELECT 'hello'" > should not have tried to use the pager, because that would've produced > less than a screenful of data At some point emacs was

Re: [GENERAL] count(*) in binary mode returns 0

imagene...@gmail.com wrote: > nfields: 1 > from_psql bytes_to_read:4 read:4 > host_order bytes_to_read:4 read:4 > conv_int ir:0 Note that count(*) produces a bigint (8 bytes), not an int (4 bytes). => select

Re: [GENERAL] Storing files: 2.3TBytes, 17M file count

Thomas Güttler wrote: > Up to now we use rsync (via rsnapshot) to backup our data. > > But it takes longer and longer for rsync to detect > the changes. Rsync checks many files. But daily only > very few files really change. More than 99.9% don't. lsyncd+rsync has worked nicely for me

Re: [GENERAL] initdb createuser commands

Samuel Williams wrote: > John - that's an interesting example. If it's that easy, why isn't > that the approach given in tutorials and other documentation? What was > the motivation for the createuser command? initdb, createdb and createuser existed even before Postgres adopted SQL, back

[GENERAL] Maximum number of exclusive locks

Hi, When deleting large objects, an exclusive lock is grabbed on each object individually. As a result, a transaction that does it en masse can encounter this error: ERROR: out of shared memory HINT: You might need to increase max_locks_per_transaction. I would expect the maximum number

Re: [GENERAL] Sequential vs. random values - number of pages in B-tree

Francisco Olarte wrote: > I think there are some pseudo-random number generators which > can be made to work with any range, but do not recall which ones right > now. There's a simple technique that works on top of a Feistel network, called the cycle-walking cipher. Described for

Re: [GENERAL] Sequential vs. random values - number of pages in B-tree

Francisco Olarte wrote: > unless you know of an easy way to generate a random permutation on the > fly without using a lot of memory, I do not. It could be done by encrypting the stream. For 32 bits integers: https://wiki.postgresql.org/wiki/Skip32 For 64 bits integers:

Re: [GENERAL] psql connection option: statement_timeout

Craig Boyd wrote: > So to put it another way: is there a list that shows what options are > available during the connection event or as part of the connection string? Yes, but it belongs to the chapter on libpq. The psql docpage merely points to it: -d dbname --dbname=dbname

Re: [GENERAL] pg_largeobject

Sridhar N Bamandlapally wrote: > due to size limitation BYTEA was not considered You could adopt for a custom table the same kind of structure that pg_largeobject has, that is an ordered series of BYTEA chunks. # \d pg_largeobject Table "pg_catalog.pg_largeobject" Column | Type |

Re: [GENERAL] long transfer time for binary data

Johannes wrote: > \lo_export 12345 /dev/null is completed in 0.86 seconds. If it's an 11MB file through a 100Mbits/s network, that's pretty much the best that can be expected. I would think the above is the baseline against which the other methods should be compared. > I sa my images

Re: [GENERAL] long transfer time for binary data

Johannes wrote: > psql > select lo_get(12345); > +ssl -compression 6.0 sec > -ssl 4.4 sec psql requests results in text format so that SELECT does not really test the transfer of binary data. With bytea_output to 'hex', contents are inflated by 2x. Can you tell how fast

Re: [GENERAL] *** QUESTION *** After successful 'BEGIN;' command -- why PGSQL_TRANSACTION_ACTIVE and not PGSQL_TRANSACTION_INTRANS?

Steve Petrie, P.Eng. wrote: > And yes, I am using pg_send_query(...) to execute the BEGIN; command, so as > to get a result resource returned. (Please see my forthcoming emailed > response to Adrian Klaver, wherein I provide the PHP source code that Adrian > requests.) After

Re: [GENERAL] Issues with german locale on CentOS 5,6,7

Thomas Kellerer wrote: > I always thought that this is a major shortcoming (if not a bug) in Postgres > that the collation support is left to the OS. > > Because it essentially means that that exactly the same query with exactly > the same data might return a different result if run on

Re: [GENERAL] md5(large_object_id)

Karsten Hilbert wrote: > On Mon, Oct 05, 2015 at 03:27:26PM +, Kevin Grittner wrote: > > > Karsten Hilbert wrote: > > > > > I am dealing with radiology studies aka DICOM data) one would > > > want an md5 function which streams in parts of a large object > >

Re: [GENERAL] clone_schema function

Melvin Davidson wrote: > "seriously flawed" is a bit of a stretch. Most sane developers would not > have schema names of one letter. > They usually name a schema something practical, which totally avoids your > nit picky exception. That's confusing the example with the problem it shows.

Re: [GENERAL] clone_schema function

Melvin Davidson wrote: > I've added error checking and verified that it now copies the > current sequnce values, table data, views and functions. The code dealing with functions is seriously flawed. Consider that part: SELECT pg_get_functiondef(func_oid) INTO qry; SELECT

Re: [GENERAL] PostgreSQL Developer Best Practices

Melvin Davidson wrote: Before ANYONE continues to insist that a serial id column is good, consider the case where the number of tuples will exceed a bigint. Don't say it cannot happen, because it can. In practice, it cannot happen. A tuple with a bigint column weighs at least 32

Re: [GENERAL] INSERT ... ON CONFLICT DO UPDATE

Rafal Pietrak wrote: CREATE TABLE vouchers (a_voucher bigint PRIMARY KEY default (random()*10)::bigint, issued date default now(), .); Generators of truly unique pseudo-random values provide a better ground for this. Consider for example:

Re: [GENERAL] INSERT ... ON CONFLICT DO UPDATE

Melvin Davidson wrote: Aside from Tom Lane's comments, it seems to me you are reinventing the wheel by generating random values for keys. Why not just use UUID http://www.postgresql.org/docs/9.5/static/datatype-uuid.html or serial

Re: [GENERAL] utf8 encoding problem with plperlu

Ronald Peterson wrote: # select * from doublezero(); INFO: double00 CONTEXT: PL/Perl function doublezero ERROR: invalid byte sequence for encoding UTF8: 0x00 at line 8, DATA line 558. CONTEXT: PL/Perl function doublezero I don't understand this. I need to pass $mspass to

Re: [GENERAL] PG 9.4.4 issue on French Windows 32 bits

Thierry Hauchard wrote: When restoring from backup (created from 8.4 database with PG_Dump 9.4.4), the log shows errors about UTF like : 2015-07-07 17:03:35 CEST ERREUR: séquence d'octets invalide pour l'encodage « UTF8 » : 0xf4 0x6c 0x65 0x20 [...] UPDATE test_table SET str_field

Re: [GENERAL] How to exclude blobs (large objects) from being loaded by pg_restore?

Alanoly Andrews wrote: This table does not occur in the listing produced in step 1 above and so cannot be edited out Yes, pg_largeobject is not mentioned but the TOC should have an entry named BLOBS, as well as other entries related to each large object. If BLOBS is removed or

Re: [GENERAL] Postgres architecture for multiple instances

Giuseppe Sacco wrote: Another important fact is about large objects, if you happen to use them: their OID is not just unique to the database, but to the whole cluster. This means that when you move a database in a cluster from a production system to a database on a test cluster, you

Re: [GENERAL] Simple Atomic Relationship Insert

Robert DiFalco wrote: I must be doing something wrong because both of these approaches are giving me deadlock exceptions. Deadlocks are to be expected if the INSERTs are batched within a single transaction and there are several sessions doing this in parallel. Given that there's an

Re: [GENERAL] Simple Atomic Relationship Insert

Roxanne Reid-Bennett wrote: When you have a sequence of steps that need to be serialized across processes, choose or even create a table to use for locking This can also be done with an advisory lock, presumably faster:

Re: [GENERAL] php password authentication failed for user ...

basti wrote: hostall all localhost md5 then I get the following error: password authentication failed for user testuser Aside from submitting a wrong password, a less obvious cause for this error message is when the password is expired. Not so long ago, there

Re: [GENERAL] libpq - lack of support to set the fetch size

matshyeq wrote: The only solution is CURSOR based which I find an awkward low level hack comparing to elegant option supported by native library. That's not the only solution, even with the current DBD::Pg you could do: $dbh-do(COPY (sql-squery) TO STDOUT); my $data; while

Re: [GENERAL] libpq - lack of support to set the fetch size

matshyeq wrote: Unfortunately, this is a limitation in the underlying driver (libpq) rather than DBD::Pg itself. There have been talks over the years of supporting this, but nothing concrete yet. Your best bet would be to ask about this on the Postgres lists Would you consider

Re: [GENERAL] UTF-8 collation on Windows?

Dev Kumkar wrote: Succeeds but as replied earlier it creates database with LC_COLLATE = 'English_United States.1252' which corresponds to Latin1. Despite windows-1252 being a monobyte encoding sharing most of LATIN1 codes and character set, it does not mean that English_United

Re: [GENERAL] problem connecting to postgres via apache

Susan Cassidy wrote: [scassidy@jacensolo ~]$ /usr/sbin/sestatus | grep SELinux SELinux status: enabled SELinuxfs mount:/selinux I have yet to find what to do to enable webserver connections. setsebool -P httpd_can_network_connect_db 1 Best regards,

Re: [GENERAL] problem connecting to postgres via apache

Susan Cassidy wrote: Cannot set persistent booleans without managed policy. Could not change policy booleans I don't know what that means. It probably means you're not root when issuing that command. Become root or use sudo. Best regards, -- Daniel PostgreSQL-powered mail user

Re: [GENERAL] psql client memory usage

Tim Kane wrote: I have a fairly simple query, running on a particularly large table. For illustration: echo select * from really_big_table; | psql my_database /dev/null See psql's FETCH_COUNT. From the manpage: FETCH_COUNT If this variable is set to an

Re: [GENERAL] What's wrong with postgresql.org domain?

Eduardo Morras wrote: In 87.238.57.232 (Sweeden) 98.129.198.126(San Antonio,TX) and 217.196.149.50(Saltzburg) i get lighttpd default page. Perhaps a miss configuration on my ISPs dns. These IP addresses look fine, but it's not unexpected that using them directly in the browser's

Re: [GENERAL] postgresql command line exploit found in the wild

Merlin Moncure wrote: if you have an internet facing database, patch it immediately! By the way: People running 9.1 on debian stable (squeeze) typically use this package: http://packages.debian.org/squeeze-backports/postgresql-9.1 Currently, it looks like the fix is only available in

Re: [GENERAL] Is there any way to listen to NOTIFY in php without polling?

Misa Simic wrote: I am not a C developer - was not aware about select()... I was read it as some kind of sleep... php provides socket_select() as an equivalent to C's select(). See http://php.net/manual/en/function.socket-select.php But it takes socket resources as arguments and the

Re: [GENERAL] High RAM usage on postgres

Merlin Moncure wrote: problem is psql buffering whole result set in memory before outputting result. note this is core problem with libpq client library until very recently. there are several easy workarounds: *) use cursor *) don't select entire table, page it out using index

Re: [GENERAL] fascinating article on postgresql mailing lists

Vincent Veyron wrote: I find it strange that 'Probability that a new thread gets a response' sits below 60% for the 'general' list This seems indeed too low. I happen to collect these messages in a database since mid-2005. As a point of comparison, the numbers I get until today

Re: [GENERAL] Linux Distribution Preferences?

Vincent Veyron wrote: On Debian/Ubuntu, the default behavior is to have SSL enabled out of the box, including for TCP connections to localhost. It is in Ubuntu, but not in Debian. No, I've seen it a number of times with Debian. pg_createcluster will enable SSL in postgresql.conf

Re: [GENERAL] VALUES() evaluation order

Tom Lane wrote: Consider sticking the nextval() into a WITH. This is also a reminder that PG's nextval() differs on this from the SQL standard, which says: If there are multiple instances of next value expressions specifying the same sequence generator within a single SQL-statement,

Re: [GENERAL] Linux Distribution Preferences?

Edson Richter wrote: Do you have any fact that support RHEL being slower than others? I would like to improve our servers if we can get some ideas - so far, we have tried Ubuntu LTS servers, and seems just as fast as RHEL for PostgreSQL (tests made by issuing heavy queries). On

Re: [GENERAL] Smaller data types use same disk space

Tom Lane wrote: That's a controversial point: doing it that way makes reordering of large tables highly impractical. In particular, if the implementation works like that, you hardly need any system support at all. You can do the equivalent today with a few SQL commands: create a

Re: [GENERAL] PostgreSQL Trigger and rows updated

Albert wrote: UPDATE hello_cars SET status = new.status WHERE OLD.ID = NEW.ID; [...] the trigger works fine. when cars table updated, the hello_cars table updated but status column in each row is updated and contains same new status ! it must be updated according to car

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

Reuven M. Lerner wrote: 1.1 1,000 records == DeleteDump ---+-+ Empty content 0.172s0.057s bytea 0.488s0.066s large object30.833s 9.275s How much bytea are you dumping

Re: [GENERAL] heavy swapping, not sure why

Lonni J Friedman wrote: ok, I'll do my best to capture this data, and then reply back. If using linux, you should find interesting data on per-process swap and memory usage in /proc/${pid}/smaps Also consider the script here:

Re: [GENERAL] duplicate key violates unique on a nextval() field

Peter Warasin wrote: The message tells me furthermore that freeradius tries to insert a record with a radacctid which already exists. No, the message you quoted tells about the other unique constraint, the one named radacct_unique. It's not related to the bigserial primary key. Best

Re: [GENERAL] SELECT Query on DB table preventing inserts

Dan Scott wrote: the insert process is unable to insert new rows into the database You should probably provide the error message on insert or otherwise describe how it's not working. Normally reading does not unintentionally prevent writing in a concurrent session. Best regards, --

Re: [GENERAL] access to lexems or access to parsed elements

Massa, Harald Armin wrote: select ts_parse('default','the quick brown fox jumped over the lazy fox') (1,the) (12, ) (1,quick) [...] (1,fox) is a set-returning-function, giving me 17 records of type pseudo-record. Stopwords still in there, so what. But: No chance of accessing

Re: [GENERAL] libpq sendQuery -- getResult not returning until all queries complete

Kelly Burkhart wrote: #define COMMANDS select current_timestamp; select pg_sleep(5); select current_timestamp You should use current_clock() instead of current_timestamp, because current_timestamp returns a fixed value throughout a transaction. Best regards, -- Daniel

Re: [GENERAL] Do we want SYNONYMS?

Tom Lane wrote: Taken at face value from a Postgres perspective, these statements seem to imply that different ownership and permissions apply to a synonym than to its referenced object; which seems like a completely horrid idea from a security standpoint. But maybe they are only

Re: [GENERAL] Do we want SYNONYMS?

Vick Khera wrote: On Mon, Dec 6, 2010 at 2:31 PM, Joshua D. Drake j...@commandprompt.com wrote: Command Prompt is currently considering writing a patch to provide synonyms to PostgreSQL. Is this something the community is interested in? Do we have use cases for it? MSSQL, DB2 and

Re: [GENERAL] Do we want SYNONYMS?

Michael C Rosenstein wrote: What is schema in this context? Oracle schema == Postgres database: a collection of objects (tables, functions, triggers, views, etc) owned by a user. That definition applies to an Oracle schema, but not to a postgres database. Objects inside a

Re: [GENERAL] alter table add column - specify where the column will go?

Fredric Fredricson wrote: But if you change the column names in the second SELECT in the UNION this is ignored: # SELECT c1,c2 FROM (SELECT 1 AS c1, 2 AS c2 UNION SELECT 2 AS c3, 1 AS c4) AS x; c1 | c2 + 1 | 2 2 | 1 Apparently, in a UNION the column names are

Re: [GENERAL] The first dedicated PostgreSQL forum

John R Pierce wrote: gee, we should have a Facebook wall As if there wasn't one already :) http://www.facebook.com/#!/group.php?gid=2324323985v=wall and a Twitter feed too http://twitter.com/postgresql Best regards, -- Daniel PostgreSQL-powered mail user agent and storage:

Re: [GENERAL] NOTIFY/LISTEN why is not a callback as notice processing.

Filonenko Michael wrote: I create simple mechanism to inform user about something in database triggers. In my front-end I use PQsetNoticeReceiver, and display messages in QTextEdit. I think about multi-user environment. I read about NOTIFY/LISTEN, but find no callback

Re: [GENERAL] Should PQconsumeInput/PQisBusy be expensive to use?

A.M. wrote: In PostgreSQL, query canceling is implemented by opening a second connection and passing specific data which is received from the first connection With libpq's PQCancel(), a second connection is not necessary. Best regards, -- Daniel PostgreSQL-powered mail user agent

Re: [GENERAL] Should PQconsumeInput/PQisBusy be expensive to use?

Michael Clark wrote: I guess I can have one thread performing the query using the non async PG calls, then from another thread issue the cancellation. Both threads accessing the same PGconn ? Yes. See http://www.postgresql.org/docs/9.0/static/libpq-cancel.html Best regards, --

Re: [GENERAL] Generate a dynamic sequence within a query

David Kerr wrote: Well, an upgrade's not on tap for a few months. Until then i'll need to figure out somethnig else. This may help: http://www.depesz.com/index.php/2007/08/17/rownum-anyone-cumulative-sum-in-on e-query/ or http://preview.tinyurl.com/mc4q6p Best regards, -- Daniel

Re: [GENERAL] installing from source in Windows

Turner, John J wrote: Bad news: Now the install process bombs out when I attempt to make (it gets Error 1 and Error 2 and backs out of the /c/program files/postgresql-9.0.0/src directory) FWIW, it works for me. Error X being too little to guess anything, maybe you should paste here

Re: [GENERAL] trying to use libpq in Ubuntu

Henri De Feraudy wrote: linux-g++ { LIBS += /usr/lib/libpq.a INCLUDEPATH += /usr/include/postgresql } Try: linux-g++ { LIBS += -lpq INCLUDEPATH += /usr/include/postgresql } Also make sure that you have the libpq-dev package installed. Best regards, -- Daniel

Re: [GENERAL] Extracting data from BYTEA column to binary file using libpq

Julia Jacobson wrote: ofstream myFile (picture.jpg, ios::out | ios::binary); myFile.write (contents); You must specify the number of bytes to write. Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org -- Sent via pgsql-general

Re: [GENERAL] What does # mean in plpgsql?

Stephen Cook wrote: What does the hash mark (#) mean in plpgsql? I saw it used in the pseudo_encrypt function @ http://wiki.postgresql.org/wiki/Pseudo_encrypt, on the line: r2 := l1 # 1366.0 * r1 + 150889) % 714025) / 714025.0) * 32767)::int; My google-fu has failed me on

Re: [GENERAL] MySQL versus Postgres

Greg Smith wrote: The problem is that few discussions happen on these lists for things like how do I get used to PostgreSQL after growing up on MySQL? And that material will never be appropriate for the PostgreSQL documentation. I would highly encourage people to migrate their

Re: [GENERAL] Comparison of Oracle and PostgreSQL full text search

zhong ming wu wrote: I always thought there is a clause in their user agreement preventing the users from publishing benchmarks like that. I must be mistaken. No you're correct. Currently, to download the current Oracle 11.2g, one must agree to:

Re: [GENERAL] prepared statements

Scott Frankel wrote: I've found that, for a table with a serial sequence key as its first column, I have to specify the key in my prepared statement or I get type errors: ERROR: column foo_id is of type integer but expression is of type character varying. Let's try: test=

Re: [GENERAL] Efficient Way to Merge Two Large Tables

Joshua Rubin wrote: I need to figure out why this is slow, and if there is any faster way. Have you considered INSERTing into a third table that would replace both source tables when it's over? The target table would initially have no index. Best regards, -- Daniel PostgreSQL-powered

Re: [GENERAL] weird empty return from select problem; periodically get no data returned - could it be a network issue?

Susan Cassidy wrote: Any explanations or ideas? The processing works, because other iterations of the program are constantly running, so the next attempt returns data, and runs as normal. However, it bugs me that sometimes a query that should work is returning no results, for no

Re: [GENERAL] Restrict allowed database names?

Sergey Konoplev wrote: What about PL/pgSQL wrapper function for CREATE DATABASE with database name check and SECURITY DEFINER option. Not possible because CREATE DATABASE can't be executed within a function (nor within a transaction). Best regards, -- Daniel PostgreSQL-powered mail

Re: [GENERAL] Can not match 0 on bytea

seil...@so-net.net.tw wrote: Data type of table1.c1 is bytea. That column stores binary data. The following matchings do not work. What is the right syntax? TIA CN --- select c1 ~ E'\000' from table1; select c1 LIKE E'%\000%' from table1; selection

Re: [GENERAL] make check failed on 8.4.2 install

adam_pgsql wrote: This is gcc version 2.95.4 if that helps? It's a very old version of gcc, and also one that has never been officially released, according to the release notes. Run aptitude show gcc-2.95 and see the description of the package. You don't want to use that as your

Re: [GENERAL] Cast char to number

Raymond O'Donnell wrote: (i) Create a new column of type numeric or integer as appropriate. (ii) update your_table set new_column = CAST(trim(both ' 0' from old_column) as numeric) (iii) Drop the old column, as well as any constraints depending on it. (iv) Rename the new column

Re: [GENERAL] No tables in postgres and template db

Marco Battelapesca wrote: A little strange thing. as you can read, the console desn't give me again postgres=# but END. Only typing q i have again postgres=# wirh cursor but the selected table with rows and columns disappears It's a pager issue. A simple way to avoid it is to have the

Re: Fwd: [GENERAL] [LibPQ] Trying PQconnectStart: it doesn't seem to connect

Alessandro Agosto wrote: I'm not yet within select/poll cycle, this is the first call that should return CONNECTION_OK or CONNECTION_BAD (refering to docs). That would be the behavior of PQconnectdb(), not PQconnectStart(). Have you read that part of the doc: quote Other states might

Re: [GENERAL] set-level update fails with unique constraint violation

Tom Lane wrote: Daniel Verite dan...@manitou-mail.org writes: But still I wonder why there is that difference in behavior between NON DEFERRABLE and DEFERRABLE INITIALLY IMMEDIATE, when the unique constraint doesn't get deferred by using SET CONSTRAINTS. In the first case, we get

Re: [GENERAL] set-level update fails with unique constraint violation

Dean Rasheed wrote: So there is quite a bit of flexibility - you may choose to have the constraint checked at any of these times: - after each row (the default for NON DEFERRABLE constraints) - after each statement (DEFERRABLE [INITIALLY IMMEDIATE]) - at the end of the transaction

Re: [GENERAL] set-level update fails with unique constraint violation

David Fetter wrote: The DEFERRED uniqueness constraints in 8.5alpha3 fix this problem That fix has a drawback: when the unique constraint is violated, the rest of the transaction runs with data that is somehow corrupted, with duplicate values being visible. It may be uneasy to predict

Re: [GENERAL] Question on how to conditionally commit or rollback a sql-request

Bergbom Staffan wrote: I get the following error: ERROR: SPI execute_plan failed executing query “Commit”: SPI_ERROR_TRANSACTION CONTEXT: Pl/pgSQL function “objectdescription_bu” line 9 at SQL statement What does this mean and what should I do to be able to do the desired check

Re: [GENERAL] Error_startup postgresql server

Joana Camacho wrote: postg...@jc:~$ /usr/local/pgsql/bin/pg_ctl start -l logfile -D /usr/local/pgsql/data1/data/ server starting postg...@jc:~$ /usr/local/pgsql/bin/createuser radius --no-superuser --no-createdb --no-createrole -P Enter password for new role: Enter it again:

Re: [GENERAL] Catastrophic changes to PostgreSQL 8.4

Craig Ringer wrote: While true in theory, in practice it's pretty unusual to have filenames encoded with an encoding other than the system LC_CTYPE on a modern UNIX/Linux/BSD machine. It depends. In western Europe, where iso-8859-1[5] and utf8 are evenly used, it's not unusual at all.

Re: [GENERAL] Large Objects and Replication question

Tatsuo Ishii wrote: However you need to use newer API of libpq to create large objects: Oid lo_create(PGconn *conn, Oid lobjId); [...] You cannot use old API lo_creat() since it relies on OID, which pgpool-II does not guarantee OIDs can be replicated. Does it mean that

Re: [GENERAL] return value for PQbinaryTuples

Merlin Moncure wrote: PQbinaryTuples is basically going to return whatever you passed into resultformat when you executed the query (in the case of PQexec, it's going to be 1 always). You mean 0 (i.e. text, not binary). And with an exception on PQexec(FETCH c) when c is a binary

Re: [GENERAL] Cannot login, with C/C++ program

Daniel wrote: I have written a C/C++ program that trys to log into a local PostgreSQL database. Here is the code: pg_conn = PQconnectdb( hostaddr = '127.0.0.1' port = '' dbname = 'TBDB' user = 'sysdba' password = 'stelmo777' connect_timeout = '10'); if (!pg_conn) {

Re: [GENERAL] passing parameters to multiple statements

Konstantin Izmailov wrote: Some companies have policy to stay DB agnostic, i.e. use standard SQL only. Good luck with that. For example, querying the lastval of a sequence, as your sample code does, already falls outside of standard SQL, AFAIK. If PQexecParams does not support

  1   2   3   >