Re: [GENERAL] help replacing expresion in plpgsql
Juan Pablo L wrote: Hi, i have a function that receives a parameter which represents days: FUNCTION aaa_recharge_account(expdays integer) i want to add those days to the CURRENT_DATE, but i do not know how to do it, i have tried several ways to replace that in an expresion like: newexpdate := CURRENT_TIMESTAMP + interval '$1 days' using expdays; (newexpdate is declared as timestamp) and many more but none work, can someone please help me to find out how can i replace that parameter into an expression that i can add to CURRENT_TIMESTAMP or any other way that i can accomplish what i need which is to add that parameter to the current timestamp. thanks!!! There are many ways. Two I can think of right away: newexpdate := CURRENT_TIMESTAMP + CAST(expdays || ' days' AS interval); newexpdate := CURRENT_TIMESTAMP + expdays * INTERVAL '1 days'; Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_dump behaves differently for different archive formats
Restoring a plain format dump and a custom format dump of the same database can lead to different results: pg_dump organizes the SQL statements it creates in TOC entries. If a custom format dump is restored with pg_restore, all SQL statements in a TOC entry will be executed as a single command and thus in a single transaction. On the other hand, each SQL statement in a plain format dump is executed individually in its own transaction, and TOC entries are irrelevant (except as comments for documentation). E.g., if a table has ACL entries for several roles and one of them is not present in the destination database, a plain format dump will restore all privileges except the ones that pertain to the missing user, while a custom format dump will not restore any privileges even for existing users. This is because all ACL related statements are in one TOC entry. Another example is a table that you try to restore into a database where the original table owner does not exist. With a plain format dump, the table is created, but will belong to the user restoring the dump, while a custom format dump will not create the table at all. This is because CREATE TABLE and ALTER TABLE ... OWNER TO are in the same TOC entry. One can argue for or against each individual behaviour, but I am surprised by the difference. Is there a deeper reason why it should remain like this or should I consider it a bug that should get fixed? Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Convert table to view 9.1
salah jubeh wrote: ERROR: could not convert table b to a view because it has triggers HINT: In particular, the table cannot be involved in any foreign key relationships. ** Error ** ERROR: could not convert table b to a view because it has triggers SQL state: 55000 Hint: In particular, the table cannot be involved in any foreign key relationships. Scenario: create table a (id int primary key); create table b (id int primary key, a_id int references a (id)); insert into a values (1); insert into b values (1,1); create table c AS SELECT * FROM b; TRUNCATE b; ALTER TABLE b DROP CONSTRAINT b_a_id_fkey; ALTER TABLE b DROP CONSTRAINT b_pkey; ALTER TABLE b ALTER COLUMN id DROP NOT NULL; CREATE RULE _RETURN AS ON SELECT TO b DO INSTEAD SELECT * FROM C; SELECT relhastriggers FROM pg_class WHERE oid = 'b'::regclass; relhastriggers t (1 row) http://www.postgresql.org/docs/current/static/catalog-pg-class.html relhastriggers bool True if table has (or once had) triggers This is what is queried when you try to convert the table into a view. So there is no way to convert your table to a view unless you are wiling to tamper with the pg_class. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Convert table to view 9.1
salah jubeh wrote: http://www.postgresql.org/docs/current/static/catalog-pg-class.html relhastriggers boolTrue if table has (or once had) triggers This is what is queried when you try to convert the table into a view. So there is no way to convert your table to a view unless you are wiling to tamper with the pg_class. I have tried the follwoing and itworks, I need to update also relhasindex UPDATE pg_class SET relhastriggers = FALSE WHERE oid = 'b'::regclass; UPDATE pg_class SET relhasindex = FALSE WHERE oid = 'b'::regclass; To be honest I do not like to play with catalog tables, so my question would be, what are the reason for (or recently had) in the case of index, or (or once had) in the case of triggers. I find the ability to convert a table to a view an extremly handy in applications were buisnes logic is modelled as views. For example, I need to refactor b, but keep it for backward compatability as updatabale view. You are right to be reluctant to tamper with pg_class. This comment in backend/commands/trigger.c explains why relhastriggers is left true: /* * We do not bother to try to determine whether any other triggers remain, * which would be needed in order to decide whether it's safe to clear the * relation's relhastriggers. (In any case, there might be a concurrent * process adding new triggers.) Instead, just force a relcache inval to * make other backends (and this one too!) rebuild their relcache entries. * There's no great harm in leaving relhastriggers true even if there are * no triggers left. */ So I guess it is just left because nobody cared enough. What keeps you from creating a copy of b: CREATE TABLE b_copy(LIKE b EXCLUDING CONSTRAINTS); DROP TABLE b; ALTER TABLE b_copy RENAME TO b; Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [pgadmin-support] Lost database
John R Pierce wrote: if the postgresql server was running when that file backup was made(*), its pretty much worthless, it will have data corruption and errors throughout. Well, it would be better than nothing. You can pg_resetxlog and manually clean up the inconsistencies. That's better than nothing at all. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] DB Audit
misspa...@tiscali.it wrote: I am using sybase ase as dbms and I would migrate to postgresql, but the absence of a built in DB audit functionality is a show stopper for me. So I would know if there is a way to get information about DB events like: server boots login logout table access. attempt to access particular objects particular user’s actions. You can use the server log file to record all these events. You won't be able to define logging for only certain tables, but it is possible to discriminate based on database or logged on user. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [HACKERS] [pgrpm-HACKERS]SPEC file for PostgreSQL
Sameer Kumar wrote: I am trying to do a custom build (and generate binary and source RPM) package for PostgreSQL. I know community already has a RPM package, but I am trying to do a custom build. I am using attached SPEC file. But I am not able to get binary rpm. rpmbuild always gives me source rpm only. Is there anything wrong with my spec file? It is incomplete. There should be a %prep, %build, %install and %files section at least. Towards end of the process I get below messages: Checking for unpackaged file(s): /usr/lib/rpm/check-files /root/rpmbuild/BUILDROOT/PostgreSQL-9.3-1.x86_64 Wrote: /root/rpmbuild/SRPMS/PostgreSQL-9.3-1.src.rpm Executing(%clean): /bin/sh -e /var/tmp/rpm-tmp.m3q9Du + umask 022 + cd /root/rpmbuild/BUILD + /bin/rm -rf /root/rpmbuild/BUILDROOT/PostgreSQL-9.3-1.x86_64 + exit 0 The file I am using is very basic (since I am just starting with the process). Does community maintain an rpm SPEC file for PostgreSQL? Can I get access to it? Start looking here: http://yum.postgresql.org/srpms/9.3/redhat/rhel-6-x86_64/repoview/postgresql93.html The source RPMs contain the spec file. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres 9.3 read block error went into recovery mode
Shuwn Yuan Tee wrote: We recently experienced crash on out postgres production server. Here's our server environment: - Postgres 9.3 - in OpenVZ container - total memory: 64GB Here's the error snippet from postgres log: ERROR: could not read block 356121 in file base/33134/33598.2: Bad address LOG: server process (PID 21119) was terminated by signal 7: Bus error [...] Can anyone suggests whether this is critical error? Does it indicate any data corruption in postgres? Yes, this is a critical error. Unless my math is off, a PostgreSQL disk file should not contain more than 131072 blocks (1GB / 8KB), so something is whacky there. But I find the second entry just as alarming. I am no hardware guy, but I believe that a bus error would indicate a hardware problem. Is there a chance that you can perform a thorough hardware check on the machine? Make sure that you have a good backup from before this happened. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] unexplainable psql exit status 1
Tim Kane wrote:
Is anyone aware of cases where psql will occasionally return an exit status
of 1, despite there being
no obvious error condition?
I have a regular (daily) cron that executes a fairly simple script of the
form:
psql --set ON_ERROR_STOP=1 -h $DB_HOST -U $DB_USER -a -v
DATE='${DATE}' -v
MM=${DATE_MM} -f duplicate_removal.sql $DB_NAME $DB_LOG 21
The execution of this duplicate_removal.sql script appears to run without
error. It’s fairly simple,
and never fails to execute in and of itself.
However, on very rare occasion, the psql session will run to completion,
without visible/logged error
– but it returns an exit status 1.
I can see that the output is correctly redirected to $DB_LOG and I can see
the successful COMMIT
response returned at the end of the session. I can also verify that the
behaviour of the script is as
expected, to completion.
The psql man page suggests that an exit status of 1 is a fatal error of its
own.
EXIT STATUS
psql returns 0 to the shell if it finished normally, 1 if a
fatal error of its own occurs
(e.g. out of memory, file not found), 2 if the connection to the
server went bad and
the session was not interactive, and 3 if an error occurred in a
script and the variable
ON_ERROR_STOP was set.
There is no indication in the postgres log file of anything adverse.
At this point my only option is to set log_statement = all, enable shell
debugging, and wait a few
months for it to happen again (I’ve seen this occur only twice, over a 2-3
month period). Has anyone
encountered this behaviour before?
According to the documentation you quoted, it would be useless to set
log_statement=all, because the problem is on the client end.
Shell debugging sounds slightly more promising, but it will probably
not tell you much more than that psql returned with exit code 1.
A quick look at the code gave me the impression that psql will always
write an error message before exiting with 1, but I may have missed a case.
Are you sure that there is nothing on stdout or stderr in the cases where
psql returns 1?
What is the last psql command that is executed?
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] unexplainable psql exit status 1
Tim Kane wrote: I’ve enabled shell debugging to be 100% sure that I’m not munging the return code anywhere. It’s entirely possible there is something going on at the shell side of things, though I fail to see how just at the minute :) The output of the script is as follows, bearing in mind that all STDERR is being redirected to STDOUT – there should be no other output to be found. I’ve verified stdout just in case, to no avail. [...] COMMIT; COMMIT Time: 0.242 ms Doesn't look like that could cause return code 1 ... Just a random idea: are there any weird characters at the end of your SQL script? But that wouldn't explain why psql sometimes emits return code 1 and sometimes not ... Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Error pg_standby 'pg_standby' is not recognized as an internal or external command!!
Tobadao wrote: Hello everyone. I'm using PostgreSQl 9.2 on the windows XP in recovery.conf use command *standby_mode = 'on' primary_conninfo = 'host=10.0.10.2 port=5432 user=postgres password = password' restore_command = 'copy 10.0.10.2\\archiver\\%f %p' restore_command = 'pg_standby -d -s 5 -t C:\pgsql.trigger.5442 10.0.10.2\\archiver\\%f %p %r 2standby.log' recovery_end_command = 'del C:\pgsql.trigger.5442'* and standby.log say 'pg_standby' is not recognized as an internal or external command, operable program or batch file. How to fix ? I don't really know much about Windows, but shouldn't it help if the PATH environment variable of the PostgreSQL process contains the directory that contains pg_standby.exe? Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What query optimisations are included in Postgresql?
N wrote: Are there documents specifying the query optimisations in Postgresql like the SQLite (http://www.sqlite.org/optoverview.html)? From the web, I can say, there are index and join optimisation, but are there anything others like Subquery flattening? There is subquery flattening: test= EXPLAIN SELECT id1 FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE id2 = id1); QUERY PLAN - Nested Loop Semi Join (cost=0.00..2.03 rows=1 width=4) Join Filter: (t1.id1 = t2.id2) - Seq Scan on t1 (cost=0.00..1.01 rows=1 width=4) - Seq Scan on t2 (cost=0.00..1.01 rows=1 width=4) (4 rows) I guess that the reason why there is no such list is that there are so many query optimizations that it would be difficult to list them all. And things are improving from release to release. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] tracking scripts...
John R Pierce wrote: On 11/26/2013 9:24 AM, Joey Quinn wrote: When I ran that command (select * from pg_stat_activity), it returned the first six lines of the scripts. I'm fairly sure it has gotten a bit beyond that (been running over 24 hours now, and the size has increased about 300 GB). Am I missing something for it to tell me what the last line processed was? that means your GUI lobbed the entire file at postgres in a single PQexec call, so its all being executed as a single statement. psql -f filename.sql dbname would have processed the queries one at a time. Yes, but that would slow down processing considerably, which would not help in this case. I'd opt for psql -1 -f filename.sql dbname so it all runs in a single transaction. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Documentation of C functions
Janek Sendrowski wrote: Is there a documentation of postgresql's C functions like SET_VARSIZE for exmaple? For things like this consult the source code. In src/include/postgres.h you'll find: /* * VARDATA, VARSIZE, and SET_VARSIZE are the recommended API for most code * for varlena datatypes. Note that they only work on untoasted, * 4-byte-header Datums! * * Code that wants to use 1-byte-header values without detoasting should * use VARSIZE_ANY/VARSIZE_ANY_EXHDR/VARDATA_ANY. The other macros here * should usually be used only by tuple assembly/disassembly code and * code that specifically wants to work with still-toasted Datums. * * WARNING: It is only safe to use VARDATA_ANY() -- typically with * PG_DETOAST_DATUM_PACKED() -- if you really don't care about the alignment. * Either because you're working with something like text where the alignment * doesn't matter or because you're not going to access its constituent parts * and just use things like memcpy on it anyways. */ The server side C API is not documented in the documentation, because it is already (hopefully) well documented in the source. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Autodocumenting plpgsql function
Rémi Cura wrote: somebody knows of a way to autodocument plpgsql function, in a docxygen style (adding tags in comments for instance, or creating doc templates to fill). It would really help to write the doc and maintain it. I am not sure what you need, but I see two ways to document a function: 1) With /** .. */ comments in the beginning. Maybe doxygen can be used to parse a database dump. 2) With COMMENT ON FUNCTION ... IS '...'; That also keeps the documentation close to where the code is, and it shows up in database dumps. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Autodocumenting plpgsql function
Rémi Cura wrote: somebody knows of a way to autodocument plpgsql function, in a docxygen style (adding tags in comments for instance, or creating doc templates to fill). It would really help to write the doc and maintain it. Typically in you comments you include special tags, like @input, then doxygen will parse it and generate an html documentation. I can't think of a way to automatically generate such documentation from within PostgreSQL, except maybe that you write a stored procedure that analyzes all functions and adds a comment with the result. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] xmlagg doesn't honor LIMIT?
Peter Kroon wrote: Is anyone able to reproduce? When I run the query below all 5 rows are returned instead of 2. Or is this the default behaviour.. SELECT xmlagg( [...] )--xmlagg FROM __pg_test_table AS dh WHERE dh.__rel=5 LIMIT 2 --OFFSET 10; According to the documentation, that query should return exactly one row since xmlagg is an aggregate. So the LIMIT 2 won't do anything to the result. You can wrap your query in a SELECT count(*) FROM (SELECT ...) AS dummy; to see how many rows you got. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] restore crashes PG on Linux, works on Windows
Chris Curvey wrote: My vendor took a dump of our something else database (which runs on Windows), did their conversion to Postgres, and then sent me back a postgres dump (custom format) of the database for me to load onto my servers for testing. I was interested to find that while I can load the dump onto a PG 9.3 server running on Windows, I'm unable to load it on either 9.2 or 9.3 running on Linux. At some point during the restore process (and it's not a consistent point), PG on Linux crashes. You mean, the database server dies? Or that there is an error message? If it is the latter, can we see the error message? I suspect that the problem is related to the encoding specified in the database dump: CREATE DATABASE TestDatabase WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'English_United States.1252' LC_CTYPE = 'English_United States.1252'; Yes, that should throw an error on a Linux system. But you should get that error consistently, different from what you write above. So my questions for the brain trust are: 1) Would you expect this to work? No, as stated above. 2) If I had to load this database on Linux, what would be the best way to go about it? (see if I can find that charset/encoding for Linux? Ask the vendor for a plain-text dump? ) You can create the database beforehand and ignore the one error from pg_restore. You can convert the custom format dump into an SQL file with pg_restore -f dumpfile.sql dumpfile.dmp Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] restore crashes PG on Linux, works on Windows
Andrew Sullivan wrote: Guess guessing, but I bet the collation is what hurts, [...] (The background for my guess: on your Linux box UTF-8 is likely the normal local encoding, but on Windows that isn't true, and 1252 is _almost_ but not quite Unicode. This bites people generally in internationalization.) I beg your pardon, but Windows-1252 has nothing to do with Unicode or UTF-8. The only connection is that Windows-1252 and UTF-8 both are ASCII supersets. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Solution for Synonyms
Thomas Kellerer wrote: mrprice22 wrote on 22.11.2013 19:25: We are in the process of moving from Oracle to PostgreSQL. We use a stored procedure to populate some reporting tables once an hour. There are two sets of these tables, set A and set B. We use synonyms to point to the “active” set of tables at any given time. The procedure works like this: 1. If set A tables are “active” truncate set B tables or if set A tables are “active” truncate set B tables. 2. Populate set B tables. 3. Set synonyms to point to set B tables. How might I accomplish the same thing in PostgreSQL? You can use a view You can also keep the tables A and B in different schemas (probably a good idea anyway) and change search_path on the client side. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PG replication across DataCenters
Kaushal Shriyan wrote: I have read on the web that Postgresql DB supports replication across data centers. Any real life usecase examples if it has been implemented by anyone. Well, we replicate a 1 TB database between two locations. It is a fairly active OLTP application, but certainly not pushing the limits of what PostgreSQL can do in transactions per second. But I get the impression that replication is widely accepted and used by now. Please also help me understand the caveats i need to take care if i implement this setup. Don't use synchronous replication if you have a high transaction rate and a noticable network latency between the sites. Wait for the next bugfix release, since a nasty bug has just been discovered. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PG replication across DataCenters
Torsten Förtsch wrote: Don't use synchronous replication if you have a high transaction rate and a noticable network latency between the sites. Wait for the next bugfix release, since a nasty bug has just been discovered. Can you please explain or provide a pointer for more information? If you mean the bug I mentioned, see this thread: http://www.postgresql.org/message-id/20131119142001.ga10...@alap2.anarazel.de Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PG replication across DataCenters
Michael Paquier wrote: On Fri, Nov 22, 2013 at 10:03 PM, Kaushal Shriyan kaushalshri...@gmail.com wrote: I am not sure i understand the difference between async and sync replication and on what scenarios i should use async or sync replication. Does it mean if it is within same DC then sync replication is the best and if it is across DC replication async is better than sync. Please help me understand. In the case of synchronous replication, master node waits for the confirmation that a given transaction has committed on slave side before committing itself. This wait period can cause some delay, hence it is preferable to use sync replication with nodes that far from each other. I am sure that you wanted to say with nodes *not* that far from each other. Basically, you have to choose between these options: - Slow down processing, but don't lose a transaction on failover (this would be synchronous, nodes close to each other) - Replicate over longer distances, but possibly lose some transactions on failover (that would be asynchronous). Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_upgrade ?deficiency
Karsten Hilbert wrote: Let me try to rephrase: Fact: pg_upgrade can NOT properly upgrade clusters which contain databases that are set to default_transaction_read_only on Question: Is this intended ? I am pretty sure that this is an oversight and hence a bug. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] expression index not used within function
LPlateAndy wrote: Just wondering what kind of execute statement (within a function) i should use to force the planner to use the index for the following?: You cannot force anything. The best you can do is to provide an index that *can* be used and keep your statistics accurate. SELECT pcode searchmatch, geometry FROM postcode WHERE (replace(lower(pcode), ' '::text, ''::text)) LIKE (replace((lower($1)::text),' '::text,''::text)||'%'::text) I assume that pcode is of type text. In that case you could create an index like CREATE INDEX my_index ON table_name ((replace(lower(pcode), ' '::text, ''::text)) text_pattern_ops); ANALYZE table_name; Such an index can be used for queries with a LIKE, if you have a constant on the right hand side that does not start with a wildcard. If you have PostgreSQL 9.2 or later, that might work out of the box in a PL/pgSQL function. In doubt, or if you have an older version, first compute the right hand side and run the query with EXECUTE. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] N prefix and ::bpchar
oka wrote: I have a question. There are the following data. create table chartbl ( caseno int, varchar5 varchar(5) ); insert into chartbl values(1, ' '); insert into chartbl values(2, ''); The same result with the following two queries is obtained. select * from chartbl where varchar5 = ' '::bpchar -- ::bpchar EXPLAIN VERBOSE SELECT * FROM chartbl WHERE varchar5 = ' '::bpchar; QUERY PLAN - Seq Scan on laurenz.chartbl (cost=0.00..27.50 rows=7 width=28) Output: caseno, varchar5 Filter: ((chartbl.varchar5)::bpchar = ' '::bpchar) (3 rows) Character varying is cast to character in this case, that's why you get this result. There are two operators = for string types: one comparing text with text, and one comparing character with character. So there has to be some casting if you compare character varying with character. Because of rule 3)b) in http://www.postgresql.org/docs/9.3/static/typeconv-oper.html the operator chosen is the one that matches one of the argument types. select * from chartbl where varchar5 = N' ' -- N prefix That is because an N'...' literal is always of type character: SELECT pg_typeof(N' '); pg_typeof --- character (1 row) The rest of the argumentation is like in the previous case. Is this specification? Are you asking if this is according to the SQL standard or if it is working as documented? I am not sure concerning the standard, but reading Syntax Rules 3) iii) of chapter 9.3 of ISO/IEC 9075-2 I get the impression that PostgreSQL does not follow the standard here. The behaviour of your first query is well documented, but there is no documentation of N'...' literals, and I personally think that it violates the principle of least astonishment that they are interpreted as character (different from E'...'). Does it continue not to change? Since that would break user applications, it will not change without a very good reason. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres Server backend process
Jayadevan M wrote: The code has these comments - When a request message is received, we now fork() immediately. The child process performs authentication of the request, Now authentication is done by the Backend process and not by the daemon? Yes. The authentication is called in InitPostgres(), which is called in PostgresMain(). Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] counterpart to LOAD
Andreas Kretschmer wrote: ist there (in 9.1) a way to unload a shared lib? It seems it's impossible since 8.1 or so, i'm right? Yes: http://www.postgresql.org/docs/9.3/static/xfunc-c.html#XFUNC-C-DYNLOAD (Presently, unloads are disabled and will never occur, but this may change in the future.) This thread might be interesting: http://www.postgresql.org/message-id/e94e14cd0912231317w441fad87gb3a4c517603a3...@mail.gmail.com Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] WITH RECURSIVE doesn't work properly for me
Jing Fan wrote: If the grouping inside CTE is executed, I don't think it would generate result like src_id | dest_id | dist +-+-- 3384 |6236 |1 3384 |1739 |2 3384 |6236 |3 3384 |1739 |4 3384 |6236 |5 3384 |1739 |6 3384 |6236 |7 3384 |1739 |8 3384 |6236 |9 3384 |1739 | 10 3384 |6236 | 11 for we have min(dist), so it should be like src_id | dest_id | dist +-+-- 3384 |6236 |1 3384 |1739 |2 other values will be eliminated by min(). It actually generate no new tuples and the iteration should stop. You forget that the grouping query only spans the second branch of the UNION, where you add the new entries. So the new entries and the old entries won't be grouped together, and the new paths that are longer than the old ones won't get removed. Unfortunately you cannot have the UNION in a subquery for recursive CTEs, but you could use arrays to achieve what you want: WITH RECURSIVE paths (path) AS ( SELECT ARRAY[src_id, dest_id] FROM edge UNION ALL SELECT edge.src_id || paths.path FROM paths, edge WHERE edge.dest_id = paths.path[array_lower(paths.path, 1)] AND edge.src_id ALL (paths.path) ) SELECT path[1], path[array_upper(path, 1)], min(array_length(path, 1)) FROM paths GROUP BY 1, 2; The whole exercise sounds a bit like homework to me. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Junk date getting uploaded into date field
Steve Crawford wrote:
There is a comment in utils/adt/formatting.c:
* This function does very little error checking, e.g.
* to_timestamp('20096040','MMDD') works
I think the place for such warnings in addition to the source-code is in
the documentation. This or similar issues with to_date have popped up on
the lists a number of times.
Perhaps a see warnings below by the to_date description in table:
http://www.postgresql.org/docs/9.3/static/functions-formatting.html#FUNCTIONS-FORMATTING-TABLE
Then under usage notes something like:
The to_date and to_timestamp functions do minimal input error-checking
and are intended for conversion of non-standard formats that cannot be
handled by casting. These functions will attempt to convert illegal
dates to the best of their ability, e.g. to_date('33-OCT-2013',
'dd-mon-') will return 2013-11-02. Users of these functions are
advised to perform whatever external error-checking they deem prudent.
I like that.
Would you write a patch and add it to the commitfest?
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Row Level Access
Maciej Mrowiec wrote: I'm working on RBAC implementation over posgresql and I was wondering is there any common technique to achieve row level access control ? So far I'm considering using WITH clause in template like this: WITH table_name AS ( SELECT . ) user_query; Which would be just prepended to any specified query. I'm fairly new to posgresql, recently I was mostly working with mongodb. So I would be glad to get some tips :D I am not sure if I understand your problem correctly, but if you want to restrict a query to a subset of rows, You might be better off with a view that has the security_barrier option set. That allows the planner to come up with a better plan. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] WITH RECURSIVE doesn't work properly for me
Jing Fan wrote:
I am sorry but I still don't understand why it doesn't work. Possibly I
misunderstand how with
recursive works?
In my opinion,
with recursive table as{
seed statement
union
recursive statement
}
In every iteration, It will just generate results from seed statement union
recursive statement and
put them into a new temporary table, and then compare the results with the
former temporary table and
check if there are any new tuples. If no new tuples, just stop iteration. Is
there any tricky things
about recursive statement?
That is correct.
Let's assume that we have three nodes A, B and C.
Also, A points to B, B points to C and C points to B.
Let's assume that we already generated (A, B, 1) and (A, C, 2)
in previous iterations.
Then the recursive statement will generate the new
rows (A, C, 2) and (A, B, 3).
The SELECT ... GROUP BY only surrounds the recursive statement,
So the result will still be (A, C, 2) and (A, B, 3).
Then the UNION will take care of the first triple, but the second
one will be added in this iteration.
And so on ad infinitum.
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] WITH RECURSIVE doesn't work properly for me
Jing Fan wrote: On Wed, Nov 6, 2013 at 8:10 AM, Albe Laurenz laurenz.a...@wien.gv.at wrote: Let's assume that we have three nodes A, B and C. Also, A points to B, B points to C and C points to B. Let's assume that we already generated (A, B, 1) and (A, C, 2) in previous iterations. Then the recursive statement will generate the new rows (A, C, 2) and (A, B, 3). The SELECT ... GROUP BY only surrounds the recursive statement, So the result will still be (A, C, 2) and (A, B, 3). Then the UNION will take care of the first triple, but the second one will be added in this iteration. And so on ad infinitum. But after this iteration, the paths will be: A B 1 B C 1 C B 1 A C 2 A B 3 in next iteration, the recursive statement will generate (A,C,2), (A,B,3), and (A,C,4), after the group by, it will still be (A,C,2) and (A,B,3) so I think it should stop after this iteration. I see, I didn't notice that. Actually there is a mistake in my explanation above, see http://www.postgresql.org/docs/9.3/static/queries-with.html#QUERIES-WITH-SELECT Recursive Query Evaluation for a detailed explanation: In step 2b, the working table is replaced with the intermediate table, so the next iteration does not see all previously generated rows, but only the ones that were generated in the previous iteration. So in our case, the working table will look like this: Initially: A B 1 B C 1 C B 1 After the first iteration: A C 2 After the third iteration: A B 3 After the fourth iteration: A C 4 ... and so on. Your GROUP BY assumes that the working table contains all previously generated rows. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] WITH RECURSIVE doesn't work properly for me
Jing Fan wrote: I use following command to get a shortest-path query: with recursive paths( src_id, dest_id, dist) as( select n1,n2,1 from nodes union select src_id, dest_id, min(dist) from ( select paths.src_id as src_id, nodes.n2 as dest_id, paths.dist+1 as dist from paths, nodes where paths.dest_id=nodes.n1 and paths.src_idnodes.n2 ) as Temp group by src_id, dest_id ) select paths.src_id, paths.dest_id, min(dist) from paths group by 1,2; It seems that this query goes into infinite loops and finally run out of disk space. However, I testrf every iteration seperately and found that it will converge after 3-4 iterations. I wonder where is the problem. Could anyone help with it? The attatchment is the test data. The attached test data suggest different table and column names, but I assume that you mean edge when you write nodes and that columns n1 and n2 are really src_id and dest_id. The endless loop occurs because there are loops in your directed graph, but you only exclude circles where beginning is equal to end. To quote three lines from your attachment: INSERT INTO edge (src_id, dest_id) VALUES (1739, 6236); INSERT INTO edge (src_id, dest_id) VALUES (6236, 1739); INSERT INTO edge (src_id, dest_id) VALUES (3384, 6236); Your recursive WITH clause (CTE) will now happily produce: src_id | dest_id | dist +-+-- 3384 |6236 |1 3384 |1739 |2 3384 |6236 |3 3384 |1739 |4 3384 |6236 |5 3384 |1739 |6 3384 |6236 |7 3384 |1739 |8 3384 |6236 |9 3384 |1739 | 10 3384 |6236 | 11 and so on to infinity, which is why you will eventually run out of space. The grouping (and any other processing in your main query) takes place only after the CTE has been calculated, so while your query would in theory return the desired result, it does so only after calculating infinitely many intermediate rows. One solution I can envision is to put a limit on the distance, for example the total count of nodes. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Junk date getting uploaded into date field
Thomas Kellerer wrote:
bsreejithin, 05.11.2013 13:14:
Not able to post the attached details as a comment in the reply box, so
attaching it as an image file :
http://postgresql.1045698.n5.nabble.com/file/n5776987/Untitled.png
It would have much easier if you had simply used copy paste to post a text
version of that SQL.
Does your mail client not allow you to do that?
But your test case is essentially this:
select to_date('33-OCT-2013', 'dd-mon-')
which indeed returns 2013-11-02 (using 9.3.1)
I don't know if this is inteded or actually a bug - I can't find anything in
the docs relating to that
behaviour.
There is a comment in utils/adt/formatting.c:
* This function does very little error checking, e.g.
* to_timestamp('20096040','MMDD') works
So at least this is not by accident.
On the other hand, I have always thought that these functions
are for Oracle compatibility, and sqlplus says:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
SQL SELECT to_date('20096040','MMDD') FROM dual;
SELECT to_date('20096040','MMDD') FROM dual
*
ERROR at line 1:
ORA-01843: not a valid month
I don't know if that should be fixed, but fixing it might break SQL
that deliberately uses the current behaviour.
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Curious question about physical files to store database
Patrick Dung wrote: I have seen some databases product that allocate small number of large files. Please correct me if I am wrong: MSSQL (one file is the data and another file for the transaction log) MySQL with InnoDB Oracle DB2 I don't know enough about DB2 and MSSQL, but you are correct with regard to InnoDB and Oracle. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] WITH RECURSIVE doesn't work properly for me
Jing Fan wrote: I have two group operations. One is inside the CTE ( union select src_id, dest_id, min(dist) ), another is outside the CTE. Do you mean that even the grouping inside the CTE will be calculated only after the CTE has been calculated? I mean the one outside the CTE. The one inside does not do anything at all, you could omit it. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] WITH RECURSIVE doesn't work properly for me
Jing Fan wrote: Why the one inside does not do anything? It won't be executed? It is executed. It might filter out the occasional row, but if you look at the example I gave you, you'll see that it won't do anything to keep it from recursing. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Curious question about physical files to store database
Patrick Dung wrote: As I have seen, some database created or pre-allocate large physical files on the file system to as the backend of the database tablespace. For Postgresql, I have observed that it created several files in the base and global directory. It may be by design, what is the pros and cons of this behavior? You are a bit unspecific; are you talking about Oracle? The disk layout is of course by design. Oracle uses large container files and keeps its data in those. As far as I know, this is to bypass file system functionality. Oracle usually recommends direct I/O and bypasses file system functionality (caching etc.) as much as possible. I guess one reason for this is that, historically, file system implementations incurred more overhead than they do now and had all sorts of other problems with larger amounts of data. These days, filesystems perform much better, so this is no longer necessary, but Oracle is quite old software. Another reason may be Oracle's quest to rule the world, and the storage layer is part of that. Lately, Oracle tries to get everybody to use ASM, their storage layer, which completely bypasses file system functionality. PostgreSQL, on the other hand, does not have the resources or intentions to write a better file system and actually uses file system capabilities like caching to improve performance. PostgreSQL keeps what Oracle calls segments as individual files in a directory structure. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Recheck conditions on indexes
Ivan Voras wrote: I'm just wondering: in the execution plan such as this one, is the Recheck Cond phase what it apparently looks like: an additional check on the data returned by indexes, and why is it necessary? I would have though that indexes are accurate enough? cms= explain analyze select * from users where other_ids-'OIB'='70328909364' or code='0023017009'; QUERY PLAN -- Bitmap Heap Scan on users (cost=8.52..39.21 rows=10 width=330) (actual time=0.042..0.044 rows=2 loops=1) Recheck Cond: (((other_ids - 'OIB'::text) = '70328909364'::text) OR ((code)::text = '0023017009'::text)) - BitmapOr (cost=8.52..8.52 rows=10 width=0) (actual time=0.035..0.035 rows=0 loops=1) - Bitmap Index Scan on users_other_ids_oib (cost=0.00..4.26 rows=9 width=0) (actual time=0.023..0.023 rows=1 loops=1) Index Cond: ((other_ids - 'OIB'::text) = '70328909364'::text) - Bitmap Index Scan on users_code (cost=0.00..4.26 rows=1 width=0) (actual time=0.012..0.012 rows=1 loops=1) Index Cond: ((code)::text = '0023017009'::text) Total runtime: 0.082 ms (8 rows) Both indexes are plain btrees, the first one is on the expression on the hstore field (other_ids-'OIB') and the second one on a plain text field. Also, why is it using the Bitmap Index Scan in both cases? A plain query for code='foo' uses a plain index scan. This is PostgreSQL 9.1. Just because there is an entry in the index does not imply that the corresponding table entry is visible for this transaction. To ascertain that, the table row itself has to be checked. PostgreSQL 9.2 introduced index only scan which avoids that additional step if it is safe to do so. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Replication and fsync
DDT wrote: According to manual, when you set synchronous_commit to on, the transaction commits will wait until master and slave flush the commit record of transaction to the physical storage, so I think even if turn off the fsync on master is safe for data consistency and data will not be lost if slave physical storage is not damaged. I don't think that this is safe. What if the master crashes and becomes corrupted as a consequence? It will start sending corrupted data to the slave, which will replay it, thus becoming corrupted itself. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Backup Question
Shaun Thomas wrote: Wrong. The database cannot check all data for consistency upon backup. For one, that would take way too long. Well, what I meant, was that it would stop the database if it couldn't apply one of the transaction logs for whatever reason. It wasn't inconsistent enough for that. :) It would not stop the database, it would stop applying the WALs and run into an endless loop (contact primary, get told that it doesn't have the WAL, try to read archive logs, fail, reiterate). If you backup the standby, then you won't have a backup_label file. You cannot restore a backup without that. Well, the backup_label gets copied to the archive log path when pg_stop_backup gets called. So, I do have it. But beyond that, I have the start/stop WAL locations, so I can get all the required files to apply, which are all that is really necessary. Moreover, recovery needs a checkpoint/restartpoint to start. Restartpoints on the standby won't be the same as checkpoints on the primary, so I believe that even with the backup_label file you would not be able to restore the data. I suppose I could build in a function to pause the backup until the restartpoint replays on the replica. Then at least, the backup starts on both systems with the same assumptions. I'm not sure if checkpoints on primary and standby are synchronized. Why don't you just shutdown the standby and take an offline backup? That could certainly be used for PITR. After the backup, restart the standby and let it catch up. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] streaming replication: could not receive data from client: Connection reset by peer
Anson Abraham wrote: No client connecting to the slave. It's just streamed replication for HA. This occurs when the slave starts immediately. SSL is used. And as I mentioned the libraries are identical on both slave and master. Interestingly, another slave that replicates from master does not have this issue. The one difference between the two slaves, the slave w/ the message is a VM. But other master dbs i have w/ slaves (that are VMs) and have no issues. So it's kind of odd, that this message occurs. So, right when the slave starts, you get the message in the log, right? Is there anything else in the log? Is there anything in the master's log around that time? Try to set log_connections=on and log_disconnections=on, then you should at least see when the slave connects. Could there be a firewall or something that filters or disconnects sessions? Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] A client and server encoding question
Amit Langote wrote: With a server initdb'd with UTF8 encoding , if I create a table with a client using LATIN1 encoding and later try to work with the relation with a client using UTF8 encoding (both the scenarios simulated using single session of psql but with different client_encoding set), there is an error. Following might help illustrate the problem: psql (9.2.4) Type help for help. postgres=# SHOW server_encoding; server_encoding - UTF8 (1 row) Time: 0.761 ms postgres=# SET client_encoding TO LATIN1; SET Time: 1.382 ms postgres=# create table id_äß(ID int); CREATE TABLE Time: 31.344 ms postgres=# \dt List of relations Schema | Name | Type | Owner +-+---+--- public | id_äß | table | amit (1 row) postgres=# SET client_encoding TO UTF8; SET Time: 1.007 ms postgres=# \dt List of relations Schema | Name | Type | Owner +--+---+--- public | id_äÃ\u009F | table | amit (1 row) postgres=# drop table id_äß; ERROR: table id_äß does not exist Time: 1.668 ms postgres=# SET client_encoding TO LATIN1; SET Time: 0.745 ms postgres=# drop table id_äß; DROP TABLE Time: 16.954 ms But, I had an impression that above shouldn't have caused any problem? Should UTF8 handle the situation gracefully? Or am I missing something? You are missing that your terminal is still running with an UTF8 locale. So when you create the table, you are feeding psql with \x69645fc3a4c39f: 69 .. i 64 .. d 5f .. _ c3a4 ä c39f ß But you told psql that you are going to feed it LATIN1, so these 7 bytes are interpreted as 7 LATIN1 characters, converted to UTF8, and the table actually has this name: \x69645fc383c2a4c383c29f because the server uses UTF8. If you change your client encoding back to UTF8, no conversion between client and server will take place, and it's hardly surprising that the server complains if you tell it to drop the table with the name \x69645fc3a4c39f. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why there are TRANS_START and TRANS_INPROGRESS
DT wrote: I'm reading code of xact.c, and I found the only difference between TRANS_START and TRANS_INPROGRESS is when transaction aborts in TRANS_START status we set status to TRANS_INPROGRESS so AbortTransaction() will not report WARNING. So I wonder to know the reason why we distinguish them? After reading into it it seems to me that TRANS_START means transaction is starting and TRANS_INPROGRESS means transaction successfully started. See StartTransaction() and StartSubTransaction(). A difference is made in IsTransactionState() where TRANS_START transactions are not reported as being in a transaction. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Backup Question
Shaun Thomas wrote: I have a revised backup process that's coming out inconsistent, and I'm not entirely sure why. I call pg_start_backup(), tar.gz the contents elsewhere, then pg_stop_backup(). Nothing crazy. Upon restore, two of my tables report duplicate IDs upon executing my redaction scripts. The duplicate records ended up having different ctid's, suggesting the log replay was incomplete. However, nothing in the restore logs suggest this is the case, and either way, the database wouldn't have come up if it were. (right?) Wrong. The database cannot check all data for consistency upon backup. For one, that would take way too long. Now, the main difference, is that I'm doing the backup process on our streaming replication node. The backup process calls the pg_start_backup() function on the upstream provider, backs up the local content, then calls pg_stop_backup() on the upstream provider. In both cases, it captures the start/stop transaction log positions to grab all involved archived WAL files. I already know the start xlog position is insufficient, because those transaction logs may not have replayed on the standby yet, so I also grab 3xcheckpoint_timeout extra older files (before backup start), just in case. So, I get no complaints of missing or damaged archive log files. Yet the restore is invalid. I checked the upstream, and those duplicate rows are not present; it's clearly the backup that's at fault. I remember having this problem a couple years ago, but I fixed it by working filesystem snapshots into the backup script. I can do that again, but it seems like overkill, honestly. If you backup the standby, then you won't have a backup_label file. You cannot restore a backup without that. Moreover, recovery needs a checkpoint/restartpoint to start. Restartpoints on the standby won't be the same as checkpoints on the primary, so I believe that even with the backup_label file you would not be able to restore the data. I'm not sure about the second point, maybe somebody can confirm or refute that. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] streaming replication: could not receive data from client: Connection reset by peer
Anson Abraham wrote: I'm on Debian Wheezy running postgres 9.3 both boxes are identical. I see in log file on slaves: LOG: could not receive data from client: Connection reset by peer That looks to me like a client that is connected to the slave is dying. Do you have hot standby turned on? Are there any other messages on master or slave around this time? Does this message come right away whan you start the slave or only after a certain time? OpenSSL is the same version on master and slaves. The libssl is also too. Do you use SSL for streaming replication? What connection options does the slave use to connect to the master? I set ssl_renegotiation=0. So not sure why i'm seeing this in the logs on my slave environments. doing research (aka googling), i see that everyone says to set ssl_renegotiation=0 but already done that. also mentions of libssl library / OpenSSL could be different versions, but that's not the case either. That would help if you have a renegotiation problem, i.e. streaming replication has a problem after a time of correct operation. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Invalid Page Header Error
Carlo Curatolo wrote: When I lauch a vacuumdb, I have an error : ERREUR: en-tête de page invalide dans le bloc 39639 de la relation base/16384/599662 With a SELECT * FROM pg_catalog.pg_largeobject Result is ERREUR: en-tête de page invalide dans le bloc 39639 de la relation base/16384/599662 I suppose that the data of LargeObject 599662 are corrupted... That is not the OID of a large object, but of a table. What do you get for SELECT oid, relname, relkind FROM pg_class WHERE relfilenode = 599662; Question is : How I could I know where this LargeObject is used ? I can, of course, use the zero_damaged_pages = on option in postgresql.conf. Then dump/restore. But in this case I didn't know where this corrupted LargeObject is used... That could result in *all* your large objects or something else to get destroyed... You have some kind of data corruption. Have there been any crashes or unusual occurrences lately? The best thing would be to check your hardware thoroughly and restore the database from a backup. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Invalid Page Header Error
Carlo Curatolo wrote: SELECT oid, relname, relkind FROM pg_class WHERE relfilenode = 599662; -- returns nothing. Maybe the wrong database? Try to find out which object this file belongs to (maybe with oid2name). No crash occurs, I have tested the hardware (memory, harddisks, RAID5, stability test...) Good. I have made a little program to read all the LargeObject of my tables, they are all readable. Good. So, I restart postgreSQL with zero_damaged_pages = on, lauch a vacuumlo and vacummdb, restart with zero_damaged_pages = off, the recheck all the LargeObject of my tables. You mean, you ran pg_dumpall, created a new cluster and imported, right? If you continue running the damaged cluster, the next problem is waiting just around the bend. The error do no occurs anymore... Seems that the corrupted LargeObject was not used...possible ? I maintain that it was a table or index that was corrupted, not a large object. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Invalid Page Header Error
Carlo Curatolo wrote: Yes I ran pg_dumpall, create a new cluster and import. Ok, cool. Everything seems fine now. How can I prevent that ? Prevent data corruption? Have good hardware, run the latest PostgreSQL fixes... Most of all, have a good backup so that you can recover. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Incorrect index being used
Jesse Long wrote: I did ALTER COLUMN SET STATISTICS 1 for each column involved after that (is that what you meant?). But it did not make much difference, but I have read the manual regarding this setting and think I understand it. Would it be a bad idea to ALTER COLUMN SET STATISTICS 10? Would that have a very negative impact on disk usage or performace? No, in the light of what Tom wrote, I was on the wrong track entirely and your best bet is probably to rewrite the query. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Can checkpoint creation be parallel?
高健 wrote: The background writer and ordinary backends might write data (for their own reasons) that the checkpointer would have otherwise needed to write anyway. And does the ordinary backends refer to the ones created when a client make a connection to PG? Yes. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Incorrect index being used
Jesse Long wrote: I have the following query, run immediately after executing VACUUM in the database. There is only one connection to the database. You should run ANALYZE, not VACUUM. The query runs for much longer than I expect it to run for, and I think this is due to it using the incorrect subplan. As you can see, subplans 1 and 3 make use of and index, but these subplans are not used. Subplans and 4 are seqscan, and they are used. How can I get PostgreSQL to use subplan 1 and 3? They are only possible if an Index Only Scan is possible, which can only be used if the respective table entries are visible for all transactions. testdb= explain analyse SELECT * FROM ARCHIVE_DOCUMENT AS r0 WHERE r0.NODE_ID = 29 AND r0.ARCHIVE_DATE = '2013-07-08 18:28:00' AND (EXISTS (SELECT r1.* FROM ARCHIVE_DOCUMENT_INDEX AS r1 WHERE r1.ARCHIVE_ID = r0.ID AND r1.NODE_ID = r0.NODE_ID AND r1.VALUE = 'BSH70002152' ) OR EXISTS ( SELECT r2.* FROM ARCHIVE_DOCUMENT_INDEX AS r2 WHERE r2.ARCHIVE_ID = r0.ID AND r2.NODE_ID = r0.NODE_ID AND r2.VALUE = 'TC212592' ) ) ORDER BY r0.ARCHIVE_DATE DESC LIMIT 10; [...] Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (alternatives: SubPlan 3 or hashed SubPlan 4)) Rows Removed by Filter: 710851 SubPlan 1 - Index Only Scan using archive_document_index_x_archive_id_node_id_value on archive_document_index r1 (cost=0.57..4.59 rows=1 width=0) (never executed) Index Cond: ((archive_id = r0.id) AND (node_id = r0.node_id) AND (value = 'BSH70002152'::text)) Heap Fetches: 0 SubPlan 2 - Seq Scan on archive_document_index r1_1 (cost=0.00..1958104.00 rows=1520 width=16) (actual time=44418.383..44558.293 rows=4 loops=1) Filter: ((value)::text = 'BSH70002152'::text) Rows Removed by Filter: 95009919 SubPlan 3 - Index Only Scan using archive_document_index_x_archive_id_node_id_value on archive_document_index r2 (cost=0.57..4.59 rows=1 width=0) (never executed) Index Cond: ((archive_id = r0.id) AND (node_id = r0.node_id) AND (value = 'TC212592'::text)) Heap Fetches: 0 SubPlan 4 - Seq Scan on archive_document_index r2_1 (cost=0.00..1958104.00 rows=1520 width=16) (actual time=41659.464..41663.342 rows=1 loops=1) Filter: ((value)::text = 'TC212592'::text) Rows Removed by Filter: 95009922 The estimates are quite off. Does ANALYZE archive_document, possibly after increasing default_statistics_target, make a difference? Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Incorrect index being used
Jesse Long wrote: There is no problem with row visibility, there is only one connection to the database - the connection I am using to do these selects. No idea why the plans cannot be used. It might be helpful to see the table and index definitions. Thanks you for the advise regarding ANALYZE. I ran ANALYZE on both tables concerned, but not much changed: Did you try increasing default_statistics_target before ANALYZE? Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Can checkpoint creation be parallel?
高健 wrote: I have one question about checkponint . That is : can checkpoint be parallel? It is said that checkpoint will be activated according to either conditions: 1)After last checkpoint, checkpoint_timeout seconds passed. 2)When shared_buffers memory above checkpoint_segments size is filled with data. My experience is that : There is only one checkpoint process there and works, and even when there are many data created during transactions, There will also be only one checkpoint process deal with it (maybe background writer or some other server process will deal it)? It would not make sense to have more than one checkpoint going on at the same time; it is a point at which recovery can begin. And also, I think when the above 1) or 2) activated checkpoint , after the checkpoint data creation, then the 1) and 2) value will be to zero and calculated again. So I think that checkpoint will not be created parallel. Is my understanding right? Yes. And if there are some official documentation from www.postgresql.org or somewhere else, I'll be very appreciative. http://www.postgresql.org/docs/9.3/static/wal-configuration.html A checkpoint is begun every checkpoint_segments log segments, or every checkpoint_timeout seconds, whichever comes first. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Large objects system
Rafael B.C. wrote: I am dealing with the old decision about hiw to store data objects and trying to understand deep the postgre system including toast, pg-largedataobject table and so on. My real doubt right now is why bytea does not gets processed by toast system even when is grow enough. Since ive read that tuples are not allowed to expand over several dtabase pages. Maybe someone has the explanaition for this behavior? What makes you think that bytea won't get TOASTed? It sure should. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Timestamp with and without timezone conversion confusion.
Tim Uckun wrote: I have the following query. [...] SELECT interval_start, (interval_start AT TIME ZONE 'Africa/Monrovia')::timestamp with time zone as interval_start_in_africa, min_datetime, min_datetime AT TIME ZONE 'Europe/Berlin' as min_datetime_in_berlin, max_datetime, max_datetime AT TIME ZONE 'America/New_York' as max_datetime_in_new_york FROM grouped_data gd When I run this query in pgadmin I get the following results interval_start,interval_start_in_africa,min_datetime,min_datetime_in_berlin,max_datetime,ma x_datetime_in_new_york 2013-10-04 15:35:00+13,2013-10-04 02:35:00+13,2013-10-04 15:35:00+13,2013-10-04 04:35:00,2013-10-04 15:39:59+13,2013-10-03 22:39:59 2013-10-04 15:25:00+13,2013-10-04 02:25:00+13,2013-10-04 15:28:11+13,2013-10-04 04:28:11,2013-10-04 15:29:59+13,2013-10-03 22:29:59 2013-10-04 15:40:00+13,2013-10-04 02:40:00+13,2013-10-04 15:40:00+13,2013-10-04 04:40:00,2013-10-04 15:44:39+13,2013-10-03 22:44:39 2013-10-04 15:30:00+13,2013-10-04 02:30:00+13,2013-10-04 15:30:00+13,2013-10-04 04:30:00,2013-10-04 15:34:59+13,2013-10-03 22:34:59 Notice that all the offsets are set to +13 which is my laptop's offset. Why don't they show the offset of Africa or Berlin or whatever? The configuration parameter TimeZone determines how timestamp with time zone is interpreted and converted to a string. The reason for that is that in PostgreSQL there is no time zone information stored along with a timestamp with time zone, it is stored in UTC. Also note then unless I explictly cast the data as timestamp with time zone all the offsets go away and it's reported as timestamp without time zone. That is because AT TIME ZONE returns a timestamp without time zone in this case, see the documentation. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Timestamp with and without timezone conversion confusion.
Tim Uckun wrote: The reason for that is that in PostgreSQL there is no time zone information stored along with a timestamp with time zone, it is stored in UTC. That seems unintuitive. What is the difference between timestamp without time zone and timestamp with time zone? I was expecting to have the time zone stored in the field. For example one row might be in UTC but the other row might be in my local time. It is unintuitive and has caused many similar complaints in the past, not least because other databases do it differently. The main difference between timestamp with time zone and timestamp without is that the former will get converted to your time zone (specified with the TimeZone parameter) automatically, while the latter always looks the same. Maybe the question I need to ask is how can I store the time zone along with the timestamp Store an additional field offset. If you want to invest more energy and don't mind writing C, you could create your own data type. That is because AT TIME ZONE returns a timestamp without time zone Also seems counterintutive but I guess I can aways convert it. I am just not getting the right offset when I convert. That's what's puzzling. I think that this is required by the SQL standard. But think of it that way: It is the answer to the question What is 2013-10-02 00:00:00 UTC in Vienna? The answer is not time zone dependent. It should be 2013-10-02 02:00:00 and not 2013-10-02 02:00:00 CEST. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Timestamp with and without timezone conversion confusion.
Karsten Hilbert wrote: Maybe the question I need to ask is how can I store the time zone along with the timestamp Store an additional field offset. If you want to invest more energy and don't mind writing C, you could create your own data type. Might not a composite type (timestamp without timezone, interval) suffice ? Depends on what you want. If all you want is store timestamp and time zone, a composite type is fine. Or does that still need some C sprinkling (for operator support, say) ? Exactly. If you want to work right for this data type that's the road you have to go. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Timestamp with and without timezone conversion confusion.
Karsten Hilbert wrote: Whatever became of the 2011 intent to implement the above that's linked to in the blog post ? You'd have to ask Alvaro. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] logging statements in PostgreSQL
Jayadevan M wrote: Thanks for the pointer. I do not really want to log the plans of queries, just the queries, execution time and a couple of other details (database,user). If I use the auto-explain module, it will end up printing the plan for all query execution in the log files? You can configure it so that only statements exceeding a certain duration will be logged. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] streaming replication not working
John DeSoi wrote: I have a 9.2.4 (CentOS 6.5) standby that has been working with no problems using log shipping. I wanted to add streaming replication which I thought would be as simple as adding primary_conninfo to recovery.conf and restarting the standby. But on restart there is no message or error about connecting to the primary for replication. pg_stat_replication is empty on the primary and I don't see any errors on the primary either. Here is what I have on the standby: postgresql.conf hot_standby = on max_wal_senders = 2 wal_level = hot_standby You should set the same parameters on the primary, else it won't work. recovery.conf standby_mode = 'on' trigger_file = '/pgsql/9.2/data/failover.trigger' primary_conninfo = 'host=localhost port=21333 user=postgres' restore_command = 'pg_standby -t /pgsql/9.2/data/failover.trigger /shared/pgbackup %f %p %r' archive_cleanup_command = 'pg_archivecleanup /shared/pgbackup %r' I have a ssh tunnel setup on localhost and have verified the replication user can connect to the primary. Am I missing something obvious? Do I have to back up the primary again to make this change? No, that shouldn't be necessary. What are the messages in the secondary's log after you start it? Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Something Weird Going on with VACUUM ANALYZE
Shaun Thomas wrote: This is PostgreSQL 9.1.9. So we've had vacuumdb -avz launched via cron at 3am for a few years now, and recently noticed some queries behaving very badly. While checking pg_stat_user_tables, I see this for several hundred of them: relname | last_analyze -+-- some_table | 13-SEP-13 03:27:13.289291 -05:00 another_table | 13-SEP-13 03:33:51.262007 -05:00 yet_another_table | 13-SEP-13 03:23:27.630734 -05:00 Yet last_vacuum shows this: relname | last_vacuum +-- some_table | 17-SEP-13 03:23:41.84311 -05:00 another_table | 17-SEP-13 03:21:25.588267 -05:00 yet_another_table | 17-SEP-13 03:21:28.944848 -05:00 So I thought to myself, Self, that's pretty freaking odd. The last vacuumdb (with analyze flag enabled) was this morning at 3am. Apparently something magical happened last Friday, and now analyze is broken somehow? Am I missing something, here? The log claims everything worked out OK: 2013-09-17 03:20:37 CDT|STATEMENT: VACUUM (VERBOSE, ANALYZE); 2013-09-17 03:37:31 CDT|LOG: duration: 2246467.567 ms statement: VACUUM (VERBOSE, ANALYZE); It does sound odd. What happens if you run VACUUM (VERBOSE, ANALYZE) manually? Are the statistics updated? Are there any warnings? These are from the same pid doing the vacuum. What's weird, is that the lines don't match up in time. The reported duration is 37 minutes, and since the vacuum launches at 3:00am, it matches with the last line. If that's the case, what on Earth is that line at 3:20 all about? The durations for the last few days have also been about 50% shorter than historically, which is mysterious all by itself. No idea about this. Is there a lot of load on the system? Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cannot commit when autoCommit is enabled error
Gurkan Ozfidan wrote:
We have been using postgresql-jdbc-8.3-604, and recently we started using
postgresql-jdbc-9.2,
everything seems working fine, except we are getting this error and could not
find the solution yet.
This related to our reporting and I could say that it is not happening every
report we run. If you
could help, I really appreciated.
Below is the part of the code and error that we are getting. Thanks.
ERROR:
ERROR in createReportTable(): Cannot commit when autoCommit is enabled.
org.postgresql.util.PSQLException: Cannot commit when autoCommit is enabled.
at
org.postgresql.jdbc2.AbstractJdbc2Connection.commit(AbstractJdbc2Connection.java:703)
at sun.reflect.GeneratedMethodAccessor65.invoke(Unknown Source)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at
org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:126)
at
org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:109)
at
org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:80)
at $Proxy0.commit(Unknown Source)
at sun.reflect.GeneratedMethodAccessor65.invoke(Unknown Source)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at
org.hibernate.jdbc.BorrowedConnectionProxy.invoke(BorrowedConnectionProxy.java:40)
at $Proxy125.commit(Unknown Source)
at
com.resolution.scheduler.dao.hibernate.SalesPAFNewDaoHibernate.runStorageSQLQuery(SalesPAFNewDaoHibern
ate.java:219)
at
com.resolution.scheduler.service.impl.SalesPAFNewManagerImpl.runStorageSQLQuery(SalesPAFNewManagerImpl
.java:151)
PART OF THE CODE:
public Integer runStorageSQLQuery(String sqlQuery) throws Exception {
int results=0;
try {
java.sql.Connection conn = getSession().connection();
boolean acs = conn.getAutoCommit();
//System.out.println(...AutoCommit status:
+acs);
conn.setAutoCommit(false);
java.sql.PreparedStatement st = conn.prepareStatement(sqlQuery);
results = st.executeUpdate();
conn.commit();
conn.setAutoCommit(acs);
st.close();
} catch (Exception e) {
System.err.println(ERROR in runStorageSQLQuery(): +
e.getMessage() + sqlQuery:
+sqlQuery);
e.printStackTrace();
}
return new Integer(results);
}
If that used to work with the old driver, it might be a bug in JDBC.
Try asking on the pgsql-jdbc mailing list.
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] need a hand with my backup strategy please...
Jamie Echlin wrote: What I'm trying to say is that I configured it to keep (60/5) * 24 segments plus a few spare, because I am switching xlog every 5 mins. But if there is heavy load then they will be generated more often than every 5 mins, so that number won't be enough. You should delete archived WAL files by age, that way you shouldn't have a problem. But I think it's a moot point because they seem to be useless unless you have all of them up to the last base backup. Which to me indicates I need to do a base backup every day...? You just need any base backup plus *all* archived WALs since the beginning of the backup. Of course you want a fairly recent backup, otherwise applying the WAL files can take very long. It is a good idea to keep more than one base backup in case something goes wrong (bad backup). Another reason to keep older backups is that you may want to recover to a point in time that lies further in the past, e.g. if it takes some time to discover a problem that requires recovery (corruption, ...). Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] need a hand with my backup strategy please...
Jamie Echlin wrote: I was planning to rely on the disk backup (of the base backup) if I wanted to restore to a version before the last on disk base backup. But your point about redundancy is good... I think I will keep two base backups, and do a base backup every day. Over the network this takes 5-10 mins or so, so not a big deal. I'll retain wals for 48 hours. Having to go to a PIT before the last hour or so would be a major undertaking anyway. I have seen cases where a software bug in the application gradually caused data in the database to be changed. It took a while to notice that. In such a case you want to have a backup from a month ago or more so that you can extract the data as they were back then and try to repair as much as possible. Also, what if a problem was introduced right before the weekend and noticed immediately afterwards? That might be more than 48 hours ago. There are other scenarios where a backup from longer ago would really help. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Rename extension?
Moshe Jacobson wrote: Is there a way to rename an installed extension? I have written an extension, but I don't like the name I originally chose, and I would therefore like to rename it. However, it is installed on a production system, from which it cannot be uninstalled, and I would like to rename it there, too. How can I do this? You could fiddle with the catalogs, i.e. update pg_extension. But try it on a test system first. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] problem in installation of postgresql-jdbc
Vivek Singh Raghuwanshi wrote: I am trying to install postgresql-jdbc but facing java error. It would be helpful to know which error you are facing. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [pgeu-general] [GENERAL] Call for design: PostgreSQL mugs
patrick keshishian wrote: One more cute idea that came to me last night. Here is a very poor attempt at it by yours truly; keep in mind I'm not a graphics artist. This image is for illustration purposes only! http://sidster.com/gallery/2013/09/10/elephant_paw.sml.jpg Can you picture a bunch of these on a meeting table? If that image does not scream Stampede!, I don't know what does. =) +1 Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] FW: Single Line Query Logging
Emre ÖZTÜRK wrote: I have sent below question months ago but I could not get any reply from you experts. I will very appreciated if you can help. PS: I have tried debug_pretty_print = off but I did not work. Is there a parameter to log any SQL query as a single line in audit logs? I have some problems in my SIEM application. If a DBA sends the query as a single line I can gather the whole query, but if he enters like UPDATE x ... y=Z .. where .. I only get the line starts with UPDATE then I can not see what is really changed in my SIEM logs. I have heard that there is a parameter do what I mean. Do you agree? There is no way to modify queries like that in the PostgreSQL log. You can set log_destination = 'csvlog' to get a PostgreSQL log in CSV format that can be parsed more easily. I don't know what the SIEM logs are that you talk about. If they log only part of the query, I'd say that they are broken and should be fixed. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to check if any WAL file is missing in archive folder
ascot.m...@gmail.com wrote: I am planing to backup archived WAL files from master to another machine, is there a way to check and make sure the backup archive file are all good and no any file missing or corrupted? The only 100% safe way would be to restore them, e.g. with a standby database. PostgreSQL 9.3 will also have xlogdump which you can run on the WAL file. If that does not gag, the file is probably ok. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] SR: pg_xlog
salah jubeh wrote: I have a question regarding streaming replication: When pg_xlog needs to be shipped in streaming replication and when not? I have faced a different issues in two existing setups, in the first setup, when shipping the pg_xlogs a time line issue has arisen, and in another setup, when not shipping the log files i get -the database system is starting up- Fatal error. You don't need log shipping for streaming replication as such. The time line problem should be solved in PostgreSQL 9.3. But it is still a good idea to use log shipping: As you describe, you will usually need archived WALs to start replication, unless the base backup is really recent. Another case is when there is an interruption or the standby cannot replicate fast enough. Then replication will fall behind and might need WAL archives to catch up. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Alternate input for user defined data type
Aram Fingal wrote:
I want to create a user defined data type but have flexible input just like,
for example, the boolean
data type where TRUE can be entered as any of (TRUE, true, T, t, YES, yes, Y,
y, 1...) and it will be
interpreted as the same thing.
So suppose I have days of the week:
CREATE TYPE days_of_week AS ENUM ('Su','M','Tu','W','Th','F','Sa');
Except that I want 'MON', 'Mon', 'mon' and 'monday' all to be interpreted as
'M' in the data type.
What is the best way to do this.? Is that what input_function is for in the
CREATE TYPE command? Do I
need to create a trigger?
Yes, the type input function converts the string representation to the internal
representation, so that's where you'd implement that in a user defined data
type.
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] MinGW compiled client library
Michael Cronenworth wrote: On 08/15/2013 10:59 AM, Michael Cronenworth wrote: The attached patches resolve the issue. Should I forward the patches on to the pgsql-hackers list for review or is this list sufficient? (First time PostgreSQL hacker.) Yes, any patches should be posted to -hackers, in this case with a archive reference to the discussion on -general. Please read http://wiki.postgresql.org/wiki/Submitting_a_Patch To make sure that the patch does not get lost, add it to the next commitfest on https://commitfest.postgresql.org/ (this is not required for bugfixes, but helps). Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] SSL connection has been closed unexpectedly
Stuart Ford wrote: We have a problem on our development database server, which supports a PHP application, which connects to it from a different server. Sometimes, around 1 in 4 page loads, it fails and reports the following error message: FATAL: terminating connection due to administrator command SSL connection has been closed unexpectedly Funny - that error message (with the SSL part included) does not appear in PostgreSQL source. Could you dig the exact error messages out of the database log? Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] please suggest i need to test my upgrade
M Tarkeshwar Rao wrote: We are upgrading our mediation product from postgres 8.1 to postgres 9.1 Can you please suggest some test cases or some issues which may hamper us? This is the first thing that comes to mind: http://petereisentraut.blogspot.co.at/2008/03/readding-implicit-casts-in-postgresql.html But you may encounter other incompatibilities. Read the release nots of all major releases between 8.2 and 9.1, but most of all, test your application well. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] archive folder housekeeping
ascot.m...@gmail.com wrote: I have enabled archive in PG (v 9.2.4): archive_mode = on archive_command = 'test ! -f /usr/local/pgsql/data/archive/%f cp %p /usr/local/pgsql/data/archive/%f' I know that pg_xlog folder is maintained by PostgreSQL automatically, when the pg_xlog folder hits to certain limit (pg_xlog is full), it will automatically archive old log files into the archive folder. Is the parameter wal_keep_segments used to control this limit? WAL files will be archived immediately after the switch to the next WAL file. pg_xlog is cleaned up, but that has nothing to do with how full it is. It is controlled by wal_keep_segments. WAL files are not always cleaned up immediately when they qualify, cleanup may lag behind a little. On the other hand, will PostgreSQL also automatically maintain the archive folder by itself or I need to do some housekeeping job to maintain it from time to time? Is there any PG manual command available to remove archived files by (archive) date/time? Can you please advise? PostgreSQL will not maintain the archives. You have to do housekeeping yourself. PostgreSQL does not know about your backup strategy (archived WAL files should not just be deleted, but backed up). Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Recovery.conf and PITR by recovery_target_time
ascot.m...@gmail.com wrote: I am trying another way to test PITR: by recovery_target_time. The test machine has the same PG version 9.2.4 and same O/S Ubuntu 12.04 64 bit.All archived WAL files are shipped and saved in /var/pgsql/data/archive, the latest time stamp of them is 2013-08-09 19:30:01, the full hot backup time is at '2013-08-09 16:47:12'. Case 1) I want to recover PG to the state before 18:03:02 that there were 6 tables deleted Case 2) Hope to recover PG to the point of time right before table TEST8 was created Transactions in master: 16:45:01 (create 4 test tables : test1, test2, test3, test4) 16:47:12 (FULL HOT BACKUP) 17:50:22 postgres=# CREATE TABLE test5 (id INTEGER PRIMARY KEY); INSERT INTO test5 VALUES (generate_series(1,400)); EXPLAIN ANALYZE SELECT COUNT(*) FROM test5; 17:57:13 postgres=# CREATE TABLE test6 (id INTEGER PRIMARY KEY); INSERT INTO test6 VALUES (generate_series(1,100)); EXPLAIN ANALYZE SELECT COUNT(*) FROM test6; postgres=# \d List of relations Schema | Name | Type | Owner +---+---+-- public | test1 | table | postgres (created before full hot backup) public | test2 | table | postgres (created before full hot backup) public | test3 | table | postgres (created before full hot backup) public | test4 | table | postgres (created before full hot backup) public | test5 | table | postgres public | test6 | table | postgres 18:03:02 postgres=# drop table test1; DROP TABLE postgres=# drop table test2; DROP TABLE postgres=# drop table test3; DROP TABLE postgres=# drop table test4; DROP TABLE postgres=# drop table test5; DROP TABLE postgres=# drop table test6; DROP TABLE postgres=# commit; WARNING: there is no transaction in progress COMMIT 18:04:34 postgres=# CREATE TABLE test7 (id INTEGER PRIMARY KEY); INSERT INTO test7 VALUES (generate_series(1,100)); EXPLAIN ANALYZE SELECT COUNT(*) FROM test7; 18:11:31 postgres=# CREATE TABLE test8 (id INTEGER PRIMARY KEY); INSERT INTO test8 VALUES (generate_series(1,100)); EXPLAIN ANALYZE SELECT COUNT(*) FROM test8; postgres=# CREATE TABLE test9 (id INTEGER PRIMARY KEY); INSERT INTO test9 VALUES (generate_series(1,100)); EXPLAIN ANALYZE SELECT COUNT(*) FROM test9; postgres=# CREATE TABLE test10 (id INTEGER PRIMARY KEY); INSERT INTO test10 VALUES (generate_series(1,100)); EXPLAIN ANALYZE SELECT COUNT(*) FROM test10; 19:26:18 postgres=# vacuum; VACUUM postgres=# begin; INSERT INTO test10 VALUES (generate_series(202,302));commit; end; BEGIN INSERT 0 101 COMMIT WARNING: there is no transaction in progress COMMIT postgres=# CREATE TABLE test11 (id INTEGER PRIMARY KEY); INSERT INTO test11 VALUES (generate_series(1,100)); EXPLAIN ANALYZE SELECT COUNT(*) FROM test11; 19:30:01 (ship the WAL file to test machine) CASE-1: '2013-08-09 17:57:55' (only 3 lines in recovery.conf) restore_command = 'cp /var/pgsql/data/archive/%f %p' recovery_target_time = '2013-08-09 17:57:55' recovery_target_inclusive = false Result: LOG: starting point-in-time recovery to 2013-08-09 17:57:55 LOG: restored log file 0001006F0066 from archive LOG: redo starts at 6F/6620 LOG: recovery stopping before commit of transaction 75891, time 2013-08-09 18:07:09.547682+08 LOG: redo done at 6F/66003DF0 FATAL: requested recovery stop point is before consistent recovery point LOG: startup process (PID 15729) exited with exit code 1 LOG: terminating any other active server processes [1]+ Exit 1 ... CASE-2: '2013-08-09 18:06:01' (only 3 lines in recovery.conf) restore_command = 'cp /var/pgsql/data/archive/%f %p' recovery_target_time = '2013-08-09 18:06:01' recovery_target_inclusive = false Result: LOG: starting point-in-time recovery to 2013-08-09 18:06:01 LOG: restored log file 0001006F00B0 from archive LOG: restored log file 0001006F009B from archive LOG: redo starts at 6F/9B20
Re: [GENERAL] inserting huge file into bytea cause out of memory
liuyuanyuan wrote: By the way, my project is about migrating Oracle data of BLOB type to PostgreSQL database. The out of memory error occurred between migrating Oracle BLOB to PostgreSQL bytea. Another question, if I can't migrate BLOB to bytea, how about oid type ? Large Objects (I guess that's what you mean with oid here) might be the better choice for you, particularly since you have out of memory problems. While bytea is always written in one piece, you can stream large objects by reading and writing them in smaller chunks. Moreober, large objects have a bigger size limit than the 1GB of bytea. The downside is that the API is slightly more complicated, and you'll have to take care that the large object gets deleted when you remove the last reference to it from your database. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Recovery_target_time misinterpreted?
Klaus Ita wrote: I have restored a Database Cluster with a recovery_target_time set to recovery_target_time = '2013-07-27 21:20:17.127664+00' recovery_target_inclusive = false now it seems the restore rather restored to some point in time (rather the 18th than the 27th). Is there an explanation for this huge gap? Is that the last 'consistent state'? Maybe the log entries created during restore can answer the question. 2013-07-30 11:15:15 UTC % LOG: starting point-in-time recovery to 2013-07-27 21:20:17.127664+00 2013-07-30 11:15:15 UTC % LOG: restored log file 00010230005C from archive 2013-07-30 11:15:15 UTC % LOG: restored log file 00010230005A from archive 2013-07-30 11:15:15 UTC % LOG: redo starts at 230/5ACD7CC0 ... ... ... 2013-07-30 14:28:45 UTC % LOG: restored log file 000102640002 from archive 2013-07-30 14:28:45 UTC % LOG: unexpected pageaddr 263/C706C000 in log file 612, segment 2, offset 442368 2013-07-30 14:28:45 UTC % LOG: redo done at 264/20698A8 2013-07-30 14:28:45 UTC % LOG: last completed transaction was at log time 2013-07-18 11:42:22.121512+00 2013-07-30 14:28:45 UTC % LOG: restored log file 000102640002 from archive cp: cannot stat `/var/tmp/xlogs_recovered_2013-07-30/wal_files/0002.history*': No such file or directory mv: cannot stat `/tmp/0002.history': No such file or directory 2013-07-30 14:28:45 UTC % LOG: selected new timeline ID: 2 cp: cannot stat `/var/tmp/xlogs_recovered_2013-07-30/wal_files/0001.history*': No such file or directory mv: cannot stat `/tmp/0001.history': No such file or directory 2013-07-30 14:28:45 UTC % LOG: archive recovery complete 2013-07-30 14:29:09 UTC % LOG: autovacuum launcher started 2013-07-30 14:29:09 UTC % LOG: database system is ready to accept connections well, that does not indicate anything for me. To me it indicates that log file 000102640002 might be corrupt. PostgreSQL stops replaying WAL after it detects a corrupt WAL record. Do you have a second copy of the WAL file? Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Recovery_target_time misinterpreted?
Klaus Ita wrote: I have restored a Database Cluster with a recovery_target_time set to recovery_target_time = '2013-07-27 21:20:17.127664+00' recovery_target_inclusive = false now it seems the restore rather restored to some point in time (rather the 18th than the 27th). Is there an explanation for this huge gap? Is that the last 'consistent state'? Maybe the log entries created during restore can answer the question. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trigger and deadlock
Loïc Rollus wrote: It's ok. Before the insert, The foreign key constraint locked the row. If transaction A and B lock the row with FK, before doing UPDATE, they were stuck. I found a solution by creating an before insert trigger with a simple SELECT FROM UPDATE on the row. You mean SELECT FOR UPDATE, richt? Great that you could figure out where the problem was. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trigger and deadlock
Loïc Rollus wrote: I've try to make some concurrency robustness test with an web server app that use Hibernate and Postgres. It seems that my trigger make deadlock when multiple thread use it. I will try to simplify examples: I have a table films(id, title,director) and a table directors(id,name,nbreFilms). I want to automaticaly count directors's films. So I have this triggers after each insert on films: CREATE OR REPLACE FUNCTION incrementDirectors() RETURNS TRIGGER AS $incDirectors$ BEGIN UPDATE directors SET nbreFilm = nbreFilm + 1 WHERE directors.id = NEW.director; RETURN NEW; END; $incDirectors$ LANGUAGE plpgsql; CREATE TRIGGER triggerIncrDirectors AFTER INSERT ON films FOR EACH ROW EXECUTE PROCEDURE incrementDirectors(); When I do a lot of INSERT films at the same time, I have this error: ** 1286785 22142 2013-07-26 13:55:25 CEST ERROR: deadlock detected 1286785 22142 2013-07-26 13:55:25 CEST DETAIL: Process 22142 waits for ShareLock on transaction 1286780; blocked by process 22426. Process 22426 waits for ExclusiveLock on tuple (365,13) of relation 2027300 of database 2026760; blocked by process 22142. 1286785 22142 2013-07-26 13:55:25 CEST HINT: See server log for query details. ** If I look in postgresql log for process, I see this (its a web app): 1.Process 22142: take a ADD request from http, 2.Process 22426: take a ADD request from http, 3.Process 22142: do INSERT of new film 4.Process 22146: do INSERT of new film 5.Process 22142: continue request (Process 22146 seems to be blocked) and do COMMIT 6.Process 22142: take a ADD request from http, 7.Process 22142: do INSERT of new film 8.DEADLOCK: process 22142 is waiting for 22146 and 22146 is waiting for 22142 I don't understant why the commit of the process 22142 won't unlock process 22426. Have you an idea? It would be interesting to know what relation 2027300 of database 2026760 is. Then you could select the offending tuple with SELECT * FROM tablename WHERE ctid='(365,13)'; What I would do is to set log_statement='all' and see what exact SQL statements are issued. Maybe Hibernate does something you do not know. It may also be interesting to query pg_locks immediately before commit to see what locks one transaction holds. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why are stored procedures looked on so negatively?
Some Developer wrote: On 24/07/13 01:55, John Meyer wrote: Taking an absolutist position either way is pretty blind. What is the purpose of the procedure? Is it enforcing business rules? Are these rules that must be enforced against already existing data or are they more akin to validation of a credit card. How many people are accessing your database at one time? And most importantly, what are you best at? Basically what happens is an object is created in the application and saved to the database. When the insert has completed I need to start a process immediately based on the information in the object on another server (Amazon Simple Message Queue to be precise). So basically I'll have a trigger function that fires on INSERTs and does this work. That way the action will only be performed on INSERTs that have successfully completed and I can be sure that the trigger will always fire. If you want to write a (trigger) function that starts a process on a remote machine, there are a few points to think about: - Should the INSERT fail if the remote process cannot be started? If yes, then a trigger is a good idea. - If you code it as a trigger, be aware that the transaction is not complete until the remote process has been started. That might be a noticable delay and might affect concurrency negatively. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] (Default) Group permissions
Michael Orlitzky wrote: I want to be able to create a database, set up the (default) group permissions, and have them work, even when a new user is added to one of the groups. Right now I don't know of a way to get default group permissions. There is none, as far as I can say. You have two options: - You have the default privileges on the roles, and every user has to run SET ROLE to the role before he or she creates objects. - Whenever you create a new user, you set default privileges for the user. I guess that the first solution is not useful until there are event triggers for database logins. The example I came up with requires two groups, and five users (two in each group): [...] If I could set up a database with (default) permissions that worked this way, I'd be happy. Right now, I can get it working temporarily with a huge mess of scripts, but if another customer-dev gets added, I have to hop in as the superuser and run O(n) commands again, where n is either the number of databases or number of users (depending on which solution you choose). I guess I can think of no better way to do it. I'd try to automatize the process as much as possible, e.g. by writing functions that create users and automatically set all the necessary default privileges. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] (Default) Group permissions
Michael Orlitzky wrote: We use Postgres for shared hosting; i.e. what most people use MySQL for. The biggest headache for us so far has been that we're unable to get group permissions set up effectively so that different groups of customers, admins, apaches, etc. can access/modify the data they need, without manual intervention from someone with root and a relatively deep knowledge of the permissions system. I posted about this a while ago, and I promised to step back and come up with an example of the type of situation I'm talking about. It's not perfect, but if I don't post it now, it'll never get finished: https://github.com/mjorlitzky/postgres-groups I took a look, but it takes more time than I'm willing to spend to actually get to your problem. Could you outline briefly what the problem is? Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Application locking
Kenneth Tilton wrote:
We want to make sure no two examiners are working on the same case at
the same time, where the
cases are found by searching on certain criteria with limit 1 to get the
next case.
A naive approach would be (in a stored procedure):
next_case_id := null;
select id into next_case_id
from cases c
where unfinished = true
and not exists (select 1 from table_lock
where table_name = 'case' and row_id = c.id)
limit 1;
if found then
insert into table_lock (table_name, row_id) values ('case',
next_case_id);
end if;
return next_case_id;
I suspect it would be possible for two users to get the same case
locked that way. Yes?
If so, would adding for update to the initial select prevent a second
caller to block on their
select until the first caller had written out the lock, effectively
preventing two callers from
locking the same case?
Change prevent to cause:
If so, would adding for update to the initial select cause a second caller
to block on their select
until the first caller had written out the lock, effectively preventing two
callers from locking the
same case?
That should work, did you test it?
Actually, I'd do it in a different way. I think that the extra
table_lock table is unnecessarily difficult.
I'd change the unfinished field to a field that can hold
three stati: open, in progress and done.
Then I'd use something like the following:
CREATE OR REPLACE FUNCTION get_next() RETURNS integer
LANGUAGE plpgsql VOLATILE STRICT AS
$$DECLARE
c CURSOR FOR SELECT id FROM cases WHERE status = 'open' FOR UPDATE;
next_id integer;
BEGIN
OPEN c;
FETCH NEXT FROM c INTO next_id;
UPDATE cases SET status = 'in_progress' WHERE CURRENT OF c;
RETURN next_id;
END;$$;
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres case insensitive searches
bhanu udaya wrote: What is the best way of doing case insensitive searches in postgres using Like. Table laurenz.t Column | Type | Modifiers +-+--- id | integer | not null val | text | not null Indexes: t_pkey PRIMARY KEY, btree (id) CREATE INDEX t_val_ci_ind ON t ((upper(val) text_pattern_ops); ANALYZE t; EXPLAIN SELECT id FROM t WHERE upper(val) LIKE 'AB%'; QUERY PLAN -- Index Scan using t_val_ci_ind on t (cost=0.01..8.28 rows=1 width=4) Index Cond: ((upper(val) ~=~ 'AB'::text) AND (upper(val) ~~ 'AC'::text)) Filter: (upper(val) ~~ 'AB%'::text) (3 rows) Thanks. But, I do not want to convert into upper and show the result. Example, if I have records as below: id type 1. abcd 2. Abcdef 3. ABcdefg 4. aaadf The below query should report all the above No, it shouldn't :^) select * from table where type like 'ab%'. It should get all above 3 records. Is there a way the database itself can be made case-insensitive with UTF8 characterset. I tried with character type collation POSIX, but it did not really help. My solution is fast and efficient, it will call upper() only once per query. I don't see your problem. Different database systems do things in different ways, but as long as you can do what you need to do, that should be good enough. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres case insensitive searches
Ingmar Brouns wrote: My solution is fast and efficient, it will call upper() only once per query. I don't see your problem. Different database systems do things in different ways, but as long as you can do what you need to do, that should be good enough. I was toying around a little bit with this example, just for my understanding, the function upper is called for every row in the result. I think this has something to to with the filter in the plan. You are right, and the function is also called once per result row. The point I was really trying to make is that it is *not* called once per row in the table. postgres=# explain select * from foo where test_upper(md5) like 'ABAAB%'; QUERY PLAN -- Index Scan using foo_ind on foo (cost=0.50..14.02 rows=250 width=33) Index Cond: ((test_upper(md5) ~=~ 'ABAAB'::text) AND (test_upper(md5) ~~ 'ABAAC'::text)) Filter: (test_upper(md5) ~~ 'ABAAB%'::text) (3 rows) So under my assumption that it is the filter that causes the function execution, I don't understand how a row can satisfy --which I read as = 'ABAAB' and 'ABAAC' ((test_upper(md5) ~=~ 'ABAAB'::text) AND (test_upper(md5) ~~ 'ABAAC'::text)) and not (test_upper(md5) ~~ 'ABAAB%'::text) I don't know, but I suspect it has to do with collations. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] CASE Statement - Order of expression processing
But in the following expression: template1=# SELECT CASE WHEN (SELECT 0)=0 THEN 0 ELSE 1/0 END; ERROR: division by zero (Just to be sure, a SELECT (SELECT 0)=0; returns true) It seems that when the CASE WHEN expression is a query, the evaluation order changes. According to the documentation, this behaviour is wrong. Just to keep you updated: We have updated the documentation to alert people to this behaviour: http://www.postgresql.org/docs/devel/static/functions-conditional.html#FUNCTIONS-CASE There were considerations to change the behaviour, but that would mean that query execution time suffers in many cases. It was decided that the problem occurs only in rather artificial queries, and that it would not be worth changing the normally useful behaviour of constant folding during query planning. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: [HACKERS] Frontend/backend protocol improvements proposal (request).
Dmitriy Igrishin wrote: Since there can be only one unnamed prepared statement per session, there should be only one such object per connection. It should not get deallocated; maybe it could be private to the connection, which only offers a parseUnnamed and executeUnnamed mathod. More precisely, there can be only one uniquely named prepared statement (named or unnamed) per session. Could you provide a signature of parseUnnamed and executeUnnamed please? I don't clearly understand this approach. I'm just brainstorming. I'm thinking of something like void Connection::prepareUnnamed(const char *query, int nParams, const Oid *paramTypes); and Result Connection::executeUnnamed(int nParams, const char * const *paramValues, const int *paramLengths, const int *paramFormats, int resultFormat); But I'm not saying that this is the perfect solution. If you really want your users to be able to set prepared statement names, you'd have to warn them to be careful to avoid the problem of name collision -- you'd handle the burden to them. That's of course also a possible way, but I thought you wanted to avoid that. The mentioned burden is already handled by backend which throws duplicate_prepared_statement (42P05) error. I mean the problem that you create a prepared statement, then issue DEALLOCATE stmt_name create a new prepared statement with the same name and then use the first prepared statement. Prepared_statement* pst1 = connection-describe(name); Prepared_statement* pst2 = connection-describe(name); // pst2 points to the same remote object That seems like bad design to me. I wouldn't allow different objects pointing to the same prepared statement. What is the benefit? Shouldn't the model represent reality? Well, then the C and C++ languages are bad designed too, because they allow to have as many pointers to the same as the user like (needs) :-) That's a different thing, because all these pointers contain the same value. So if pst1 and pst2 represent the same object, I'd like pst1 == pst2 to be true. Really, I don't see bad design here. Describing prepared statement multiple times will results in allocating several independent descriptors. ... but for the same prepared statement. (As with, for example, performing two SELECTs will result in allocating several independent results by libpq.) But those would be two different statement to PostgreSQL, even if the query strings are identical. Mind you, I'm not saying that I am the person that decides what is good taste and what not, I'm just sharing my sentiments. Of course an error during DEALLOCATE should be ignored in that case. It's hard to conceive of a case where deallocation fails, but the connection is fine. And if the connection is closed, the statement will be deallocated anyway. Why this error should be ignored? I believe that this should be decided by the user. As a library author I don't know (and cannot know) how to react on such errors in the end applications. Again, I would say that that is a matter of taste. I just cannot think of a case where this would be important. Btw, by the reason 2) there are no any transaction RAII classes as in some other libraries, because the ROLLBACK command should be executed in the destructor and may throw. I tend to believe that such errors could also be ignored. If ROLLBACK (or anything else) throws an error, the transaction will get rolled back anyway. Perhaps, but, again, I don't know how the user will prefer to react. So, I prefer just to throw and allow the user to decide. Agreed, it's a matter of taste. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] auto_explain FDW
David Greco wrote: In my development environment, I am using the auto_explain module to help debug queries the developers complain about being slow. I am also using the oracle_fdw to perform queries against some oracle servers. These queries are generally very slow and the application allows them to be. The trouble is that it appears auto_explain kicks in on the query to try and explain them when they take longer than the configured threshold. In this particular case, the Oracle user is very locked down and cannot actually perform an explain. Therefore an error gets raised to the client. I would suggest one of two things- either make the error that gets raised simply be a notice/warning, or preferably just add an option to auto_explain to enable/disable its operation on queries involving foreign servers. I'm reluctant to change oracle_fdw to not throw an error if it doesn't have the permission to explain the query when you ask it to --- for one, what should it return in that case? I'd say that the solution in this case would be to temporarily allow the user to query the necessary Oracle catalogs. If you debug in a production scenario, you'll have to make compromises (similar to granting the PLUSTRACE role if you want to use AUTOTRACE with SQL*Plus). Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres case insensitive searches
bhanu udaya wrote: What is the best way of doing case insensitive searches in postgres using Like. Table laurenz.t Column | Type | Modifiers +-+--- id | integer | not null val| text| not null Indexes: t_pkey PRIMARY KEY, btree (id) CREATE INDEX t_val_ci_ind ON t ((upper(val) text_pattern_ops); ANALYZE t; EXPLAIN SELECT id FROM t WHERE upper(val) LIKE 'AB%'; QUERY PLAN -- Index Scan using t_val_ci_ind on t (cost=0.01..8.28 rows=1 width=4) Index Cond: ((upper(val) ~=~ 'AB'::text) AND (upper(val) ~~ 'AC'::text)) Filter: (upper(val) ~~ 'AB%'::text) (3 rows) Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to REMOVE an on delete cascade?
Phoenix Kiula wrote: Hi. Hard to find this command in the documentation - how should I alter a table to REMOVE the on delete cascade constraint from a table? Thanks. Unless you want to mess with the catalogs directly, I believe that you have to create a new constraint and delete the old one, like: Table laurenz.b Column | Type | Modifiers +-+--- b_id | integer | not null a_id | integer | not null Indexes: b_pkey PRIMARY KEY, btree (b_id) b_a_id_ind btree (a_id) Foreign-key constraints: b_a_id_fkey FOREIGN KEY (a_id) REFERENCES a(a_id) ON DELETE CASCADE ALTER TABLE b ADD CONSTRAINT scratch FOREIGN KEY (a_id) REFERENCES a(a_id); ALTER TABLE b DROP CONSTRAINT b_a_id_fkey; ALTER TABLE b RENAME CONSTRAINT scratch TO b_a_id_fkey; Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: [HACKERS] Frontend/backend protocol improvements proposal (request).
Dmitriy Igrishin wrote: I understand the problem now. I pondered a bit over your design, and I came up with a different idea how to represent prepared statements in a C++ library. First, a prepared statement is identified by its name. To make the relationship between a PreparedStatement object and the PostgreSQL prepared statement unique, I suggest that the prepared statement name should not be exposed to the library user. It should be a private property that is set in the initializer in a unique fashion (for example, it could be a string representation of the memory address of the object). That way, there can never be a name collision. That should take care of the problem. In fact something like was implemented in very early versions of my library. There are some reasons why I've redesigned the library: 1) If the user does not specify the name of the prepared statement (or specify it as ) it is considered as unnamed prepared statement -- a one of the important concepts of the frontend/backend protocol, which is a base of my current design. The unnamed prepared statements are very useful since they are deallocated authomatically when the backend receives the next Parse message with empty name. If you want unnamed prepared statements in your library, I would use a different class for them since they behave quite differently. That would also make this concern go away. Since there can be only one unnamed prepared statement per session, there should be only one such object per connection. It should not get deallocated; maybe it could be private to the connection, which only offers a parseUnnamed and executeUnnamed mathod. 2) Meaningful names of the named prepared statements (as any other database objects) may be useful while debugging the application. Imagine the memory addresses (or any other surrogate names) in the Postgres logs... That wouldn't worry me, but that's a matter of taste. Hence, the name() method should be public and name().empty() means unnamed prepared statement. You could offer a getter for the name if anybody needs it for debugging. If you really want your users to be able to set prepared statement names, you'd have to warn them to be careful to avoid the problem of name collision -- you'd handle the burden to them. That's of course also a possible way, but I thought you wanted to avoid that. I also wouldn't provide a deallocate() method. A deallocated prepared statement is useless. I think that it would be more logical to put that into the destructor method. If somebody wants to get rid of the prepared statement ahead of time, they can destroy the object. I've also considered this approach and there are some reasons why I don't implemented the prepared statement class this way: 1) There are Describe message in the protocol. Thus, any prepared statement can be also described this way: Prepared_statement* pst1 = connection-describe(name); Prepared_statement* pst2 = connection-describe(name); // pst2 points to the same remote object Think about the pst as a pointer to the remote object (prepared statement). Since each statement can be described multiple times, the deleting one of them should not result in deallocating the prepared statement by the backend. That seems like bad design to me. I wouldn't allow different objects pointing to the same prepared statement. What is the benefit? Shouldn't the model represent reality? 2) The best way to inform the user about errors in the modern C++ are exceptions. The dellocate operation (as any other query to the database) can be result in throwing some exception. But descructors should not throw. (If you are familiar with C++ well you should know about the gotchas when destructors throw.) So, there are deallocate() method which seems to me ok. Of course an error during DEALLOCATE should be ignored in that case. It's hard to conceive of a case where deallocation fails, but the connection is fine. And if the connection is closed, the statement will be deallocated anyway. Btw, by the reason 2) there are no any transaction RAII classes as in some other libraries, because the ROLLBACK command should be executed in the destructor and may throw. I tend to believe that such errors could also be ignored. If ROLLBACK (or anything else) throws an error, the transaction will get rolled back anyway. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] utf8 errors
Jirí Pavlovský wrote: I have a win32 application. LOG: statement: INSERT INTO recipients (DealID, Contactid) VALUES (29009, 9387) ERROR: invalid byte sequence for encoding UTF8: 0x9c But the query is clean ascii and it doesn't even contain the mentioned character. My database is in UNICODE, client encoding is utf8. Could you run the log message through od -c on a UNIX machine and post the result? Maybe there are some weird invisible bytes in there. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] utf8 errors
Jirí Pavlovský wrote: I'm getting these errors on tables as well. Actually when I copy and paste the offending queries from log into pgAdmin it runs without an error. So the queries work from pgadmin; what application/environment are they NOT working in? Something is obviously different. You say it's a Win32 application, what database libraries and programming languages are involved? I'm using plain c and libpq from 9.2.2. And gtk as a GUI. Compiler is mingw (gcc for windows). Does the application perhaps send trailing garbage after the query or something similar? Something like that might happen if there's a memory allocation bug in the application. I'm assuming here that, if the query string cannot be converted from utf-8 due to garbage characters, the transcoding error triggers before the query parser notices a syntax error. Could be. But when I look at the query string in gdb, before it is send, I don't see there anything problematic. I guess I'll have to try to wite some test cases to try to locate the problem. Once you can reproduce the problem, try a network trace on the communication between cleint and server. Maybe that helps to solve the problem. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: [HACKERS] Frontend/backend protocol improvements proposal (request).
Dmitriy Igrishin wrote: While developing a C++ client library for Postgres I felt lack of extra information in command tags in the CommandComplete (B) message [...] It seems like bad design to me to keep a list of prepared statements on the client side when it is already kept on the server side (accessible with the pg_prepared_statements view). What's wrong with the following: If the user wants to deallocate an individual prepared statement, just send DEALLOCATE statement name to the server. If the statement does not exist, the server will return an error. If the user wants to deallocate all statements, just send DEALLOCATE ALL. Why do you need to track prepared statements on the client side? Thats great, but there is a some problem -- the *another* statement with the same name (and moreover with same parameters!) can be prepared after deallocating. And this can result in bugs. So, the client-side allocated pointer to the remote statement must be invalidated immediatly after deallocating. I understand the problem now. I pondered a bit over your design, and I came up with a different idea how to represent prepared statements in a C++ library. First, a prepared statement is identified by its name. To make the relationship between a PreparedStatement object and the PostgreSQL prepared statement unique, I suggest that the prepared statement name should not be exposed to the library user. It should be a private property that is set in the initializer in a unique fashion (for example, it could be a string representation of the memory address of the object). That way, there can never be a name collision. That should take care of the problem. Of course somebody could find out what the statement name is and manually issue a DEALLOCATE, but that would only cause future use of the prepared statement to fail with an error, which I think is ok. Also, if somebody uses SQL PREPARE to create a prepared statement whose name collides with one of the automatically chosen names, they get what they deserve in my opinion. It might be useful to warn users. I also wouldn't provide a deallocate() method. A deallocated prepared statement is useless. I think that it would be more logical to put that into the destructor method. If somebody wants to get rid of the prepared statement ahead of time, they can destroy the object. Does that make sense? Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] File size consideration of file_fdw in PostgreSQL
Xiaobo Gu wrote: We have very large files in size will be created as external tables in PostgreSQL via file_fdw, we have the following considerations: 1. Can file_fdw handle files with size large than the size of RAM in the server. That shouldn't be a problem. 2. Will file_fdw scan the full file every time when the external table is queried, even we have where clause to narrow to a subset of rows , as the file_fdw doesn't have a server engine. Yes, the complete file will be read whenever the foreign table is queried. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] WAL archiving not starting at the beginning
Michael Angeletti wrote: I'm Michael, and this is my first post here. I asked this question last night: http://dba.stackexchange.com/questions/45077 which details the problem (or not?) I'm having. The gist of it is that Postgres is not archiving the first WAL segments for my cluster. You'll note that I changed the wal_level from minimal to hot_standby at the same time as I turned archiving on. I'm not sure if A) that's a no-no (changing the wal_level after a new cluster has started once), B) it's a bug/problem to have my archives starting at the 6th log, despite the first 5 still being in pg_xlog, C) if my switching of wal_level has anything to do with the issue, and D) if I need to start over with a fresh database or something, in order to remedy the issue. I can't answer the question without digging deeper, but I wonder why you care. It is not necessary to have all WAL segments archived, but only the ones after your first base backup. And you won't be able to make a base backup before you turn wal_level to archive or higher. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
