Re: [GENERAL] Question about SELECT statements with subselects

Miloslav Semler wrote: > I found strange behavior with subselects and I am not able to explain > it. I have several tables in schema: > > tramecky, mt_hodnoty, plata_kusy > > in these tables, id is always primary key (serial), table_id is always > foreign key to table. When I run this query: >

Re: [GENERAL] VM-Ware Backup of VM safe?

Thomas Güttler wrote: > We run a PostgreSQL 9.6 server in a virtual machine. > > The virtual machine is managed by the customer. > > He does backup the VM. > > Is this enough, is this safe? I don't know about VMware, but the general rule is that if the backup is truly atomic (it is guaranteed

Re: [GENERAL] org.postgresql.util.PSQLException: FATAL: terminating connection due to administrator command

Emi wrote: > Running psql table updates() by using > org.springframework.scheduling.quartz.JobDetailFactoryBean cronjob from > web application. Got the following exception: > > org.postgresql.util.PSQLException: FATAL: terminating connection due to > administrator command > > > Re-run the same

Re: [GENERAL] Schemas and foreign keys

marcelo wrote: > Recently I asked regarding schemas, and received very useful answers. I > conclude that I can put some tables in one schema and left others in the > public one. > If my app selects some schema, the backend will found automatically the > absent tables in "public". > So far, so

Re: [GENERAL] Changing collate & ctype for an existing database

rihad wrote: > Btw, can we wrap the update of datcollate and rebuilding of > textual indices inside a transaction with effectively 0 downtime? No. Building indexes takes time and will lock the tables until the transaction is done. Yours, Laurenz Albe -- Sent via pgsql-general mailing list

Re: [GENERAL] DATA Integrity & Recovery

chris faber wrote: > Postgres 9.2 > > We have a POSTGRES database that we have been backing up via Incremental > backups. You are talking of a physical base backup and WAL archives, right? > We had an incident where we had to recover from backup. Our software vendor > has completed > a

Re: [GENERAL] Changing collate & ctype for an existing database

rihad wrote: > Hi there. We have a working database that was unfortunately created by > initdb with default ("C") collation & ctype. All other locale specific > settings have the value en_US.UTF-8 in postgresql.conf. The database > itself is multilingual and all its data is stored in UTF-8.

Re: [GENERAL] loading file with en dash character into postgres 9.6.1 database

Tom Lane wrote: > "Hu, Patricia" writes: >> The server and client encoding are both set to UTF8, and according to this >> http://www.fileformat.info/info/unicode/char/2013/index.htm en dash is a >> valid UTF8 >> character, but when running a script with insert statement

Re: [GENERAL] tsquery error

Stephen Davies wrote: > The following query give the error: > > select > id,title,dtype,source,used_for,ts_headline('english',content,to_tsquery('english',' > ma waterflux'),'minWords = 99, maxWords = 999') from info where clob @@ > to_tsquery('english',' ma waterflux') order by

Re: [GENERAL] psql doesn't pass on exported shell environment functions

David G. Johnston wrote: >> It works for me on Linux with 9.6.3 psql: > > ​Except you haven't recreated the scenario I presented.​ > > ​You only are involving a single script and that script defines > "testfunction" itself > (which makes exporting pointless). In my example the script being

Re: [GENERAL] psql doesn't pass on exported shell environment functions

David G. Johnston wrote: > In hindsight I'm not surprised but couldn't find a ready explanation on the > web and > figured I'd inquire here. In short: "export VAR" and "export -f > functionname" behave > differently when psql is acting as a relay. It works for me on Linux with 9.6.3 psql:

Re: [GENERAL] Feature proposal, DBURL: psql pgsql://joe:p4zzw...@example.org:2345/dbname

Hans Schou wrote: > The dburl (or dburi) has become common to use by many systems connecting to a > database. > The feature is that one can pass all parameters in a string, which has > similar pattern as > http-URI do. > > Especially when using psql in a script, having the credentials in one

Re: [GENERAL] Re: Unable to understand index only scan as it is not happening for one table while it happens for other

rajan wrote: > why the index-only scan *works only* with an *order by*? > localdb=# explain analyse verbose select uid from mm where uid>100 *order > by* uid; > QUERY > PLAN >

Re: [GENERAL] Writing a C function to return the log file name

Kouber Saparev wrote: > I am trying to write a function in C to return the log file name by given > timestamp. I > will use that later to make dynamic creation of a foreign table (file_fdw) to > read the csv > logs themselves. The thing is I do now want to hardcode neither the format, > nor the

Re: [GENERAL] plpgsql function with offset - Postgres 9.1

Patrick B wrote: > I am running a background task on my DB, which will copy data from tableA to > tableB. For > that, I'm writing a PL/PGSQL function which basically needs to do the > following: > > > 1.Select the data from tableA > 2.The limit will be put when calling the function >

Re: [GENERAL] libpg sources

Igor Korot wrote: > Can I put libpg sources into my project? Or I will have to provide binaries? You can do anything you want as long as you stick with the license: https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob_plain;f=COPYRIGHT;hb=HEAD All you have to do is include the following

Re: [GENERAL] Memory consumption for Query

dhaval jaiswal wrote: > How to check how much memory query is consuming. > > Is there tool can check of query consuming memory for the execution or output. > > Let's say for following query how to calculate memory consumption. > > select * from test where id=1; That query will not consume

Re: [GENERAL] expensive function in select list vs limit clause

Chris Mair wrote: > I've found a (simple) situation where the planner does something I don't > understand. > > Below is a complete test case followed by output. > > From the timings it appears that in the second explain analyze query a > function > call in the select list (expensive()) is

Re: [GENERAL] Request to confirm which command is use for exclusive operation

Yogesh Sharma wrote: > I observed there is some problem in REINDEX operation in older PostgreSQL > versions. > That why i want to add explicitly lock. Which problem? Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

Re: [GENERAL] Postgres, apps, special characters and UTF-8 encoding

Ken Tanzer wrote: > Hi. I've got a recurring problem with character encoding for a > Postgres-based web PHP app, and am > hoping someone can clue me in or at least point me in the right direction. > I'll confess upfront my > understanding of encoding issues is extremely limited. Here goes. >

Re: [GENERAL] Request to confirm which command is use for exclusive operation

Yogesh Sharma wrote: > I want to apply explicitly lock mechanism once inset operation is in > progress then REINDEX will wait. > And vice versa. > So, please let me know this type of handling is possible. Maybe I misunderstand something, but you don't need to do that because it happens

Re: [GENERAL] Question about TOAST table - PostgreSQL 9.2

Patrick B wrote: > I have a database which is 4TB big. We currently store binary data in a bytea > data type column > (seg_data BYTEA). The column is behind binary_schema and the files types > stored are: pdf, jpg, png. > Questions: > > 1 - If I take out 500GB of bytea data ( by updating the

Re: [GENERAL] Missing feature - how to differentiate insert/update in plpgsql function?

hubert depesz lubaczewski wrote: > I have a function, in PostgreSQL 9.6, which does: > > INSERT INTO table () values (...) > ON CONFLICT DO UPDATE ...; > > The thing is that the function should return information whether the row > was modified, or created - and currently it seems that this is

Re: [GENERAL] Running out of memory the hard way ...

Bill Moran wrote: > If you run a transaction with lots of server side functions that use a > lot of memory, this can trigger the OOM killer in Linux, causing the > PosgreSQL backend to receive a SIGKILL and all the associated bad > stuff. > > Tuning the OOM killer is not sufficient. No setting

Re: [GENERAL] Why is materialized view creation a "security-restricted operation"?

Joshua Chamberlain wrote: > I see this has been discussed briefly before[1], but I'm still not clear on > what's happening and why. > > I wrote a function that uses temporary tables in generating a result set. I > can use it when creating > tables or views, e.g., > CREATE TABLE some_table AS

Re: [GENERAL] change type from NUMERIC(14,4) to NUMERIC(24,12)

Torsten Förtsch wrote: > we have a large table and want to change the type of one column from > NUMERIC(14,4) to NUMERIC(24,12). > If the new type is just NUMERIC without any boundaries, the operation is > fast. If (24,12) is > specified, it takes ages. > > I think it takes so long because the

Re: [GENERAL] Querying dead rows

Rakesh Kumar wrote: > Is there a way to query dead rows (that is, rows which are dead and still not > cleaned up by Vacuum) > using SQL. I am asking this just as an academical question. Sort of. You can use heap_page_item_attrs() from the pageinspect contrib module to get at the data, but you

Re: [GENERAL] When updating row that has TOAST column, is the TOAST column also reinserted ? Or just the oid to the value?

Dorian Hoxha wrote: > When updating row that has TOAST column, is the TOAST column also inserted ? > Or just the oid? > > Say I have a 1MB value in the TOAST column, and I update the row by changing > another column, and since > every update is an insert, will it also reinsert the toast-column

Re: [GENERAL] WAL

Torsten Förtsch wrote: > if I do something like this: > > BEGIN; > UPDATE tbl SET data='something' WHERE pkey='selector'; > UPDATE tbl SET data=NULL WHERE pkey='selector'; > COMMIT; > > Given 'selector' actually exists, I get a separate WAL entry for each of the > updates. My question is, >

Re: [GENERAL] Rounding Problems?

elbriga wrote: > Thanks for the detailed answer! > > Changing the function sinature seams to have solved the problem: > CREATE OR REPLACE FUNCTION ceilDecimal(num numeric) RETURNS float AS > $BODY$ > BEGIN > RETURN CEIL(num * 100) / 100; > END > $BODY$ > LANGUAGE 'plpgsql'; > > > SELECT

Re: [GENERAL] Rounding Problems?

elbriga wrote: > Hi, > I have this pl function: > CREATE OR REPLACE FUNCTION ceilDecimal(num float) RETURNS float AS > $BODY$ > BEGIN > RETURN CEIL(num * 100) / 100; > END > $BODY$ > LANGUAGE 'plpgsql'; > > It is supposed to do a "decimail ceil" for 2 decimal places. > But when I do

Re: [GENERAL] min/max_wal_size

Torsten Förtsch wrote: > Now, I have a database with very low write activity. Archive_command is > called about once per hour to > archive one segment. When the database was moved to PG 9.5, it was initially > configured with insanely > high settings for max_wal_size, min_wal_size and

Re: [GENERAL] Converting a TimestampTz into a C# DateTime

valeriof wrote: > BTW, a comment says this about the floating point representation: "A > deprecated compile-time option of PostgreSQL switches to a floating-point > representation of some date/time > fields. Npgsql (currently) does not support this mode." Is it safe to say > that the floating

Re: [GENERAL] How the Planner in PGStrom differs from PostgreSQL?

Mark Anns wrote: > How the planning factors of PGStrom differs from planning factos of > PostgreSQL? I don't know what exactly you mean by a "planning factor". What PGStrom does is estimate the cost of the GPU operations and attach these costs to a custom scan node which is part of a query plan.

Re: [GENERAL] Converting a TimestampTz into a C# DateTime

Jerome Wagner wrote: > seeing you answer I have a question for which I found no answer a few weeks > ago : is there a way to > know at runtime which internal representation timestamps have ? > I am trying to deal with the COPY binary protocol with only SQL access to the > remote server and would

Re: [GENERAL] Converting a TimestampTz into a C# DateTime

valeriof wrote: > I'm handling a TimestampTz value inside a plugin to stream WAL changes to a > .NET client application. What I'm trying to do is to return all possible > column changes as binary (don't like to have Postgres handle the conversion > to string as I may need to have access to the

Re: [GENERAL] Surviving connections after internet problem

Durumdara wrote: >> You can use pg_terminate_backend to kill a database session. >> >> Setting the keepalive options in postgresql.conf can make PostgreSQL >> discover dead connections more quickly. > > The server is licenced, so we can't access the conf file now. > We will report this to the

Re: [GENERAL] Linux equivalent library for "postgres.lib" from Windows

John R Pierce wrote: >> I am new to the product and in windows “postgres.lib” provides certain >> functions which we are >> using in windows for creating extensions. >> >> Now I am porting the project to Linux and there no straight library with >> this name in Linux >> binaries packages. >> >>

Re: [GENERAL] Surviving connections after internet problem

Durumdara wrote: > Linux server, 9.4 PG, Windows clients far-far away. > > They called us that they had an "internet reset" at 13.00, but many client > locks are alive now > (14:00). > I checked server status, and and saw 16 connections. > > In Windows PG server I read about keepalive

Re: [GENERAL] Database Recovery from Corrupted Dump or Raw database table file.

Howard News wrote: > I have a raid catastrophe which has effectively blitzed a cluster data > directory. I have several pg_dump backups but these will not restore > cleanly. I assume the disk has been failing for some time and the > backups are of the corrupted database. > > Using a selective

[GENERAL] Re: What is the best thing to do with PUBLIC schema in Postgresql database

Patricia Hu wrote: > Since it could potentially be a security loop hole. So far the action taken > to address it falls into > these two categories: > > drop the PUBLIC schema altogether. One of the concerns is with some of > the system objects that > have been exposed through PUBLIC schema

Re: [GENERAL] postgres_fdw : disable extended queries

Nicolas Paris wrote: > I have a 9.6 pg instance, and I am trying to link a foreign postgresql > database that do not accept > extended queries. (only simple queries > https://www.postgresql.org/docs/current/static/protocol.html ) > > When I run a query against the foreign pg instance thought

Re: [GENERAL] Sequences / Replication

Jonathan Eastgate wrote: > We're seeing some odd behaviour from a PostgreSQL group - one running as > primary and the other as a > hot slave using streaming replication. > > When a failover event occurs and we switch to the hot slave as primary > sequences in tables jump by 33 > - so where the

Re: [GENERAL] journaled FS and and WAL

t.dalpo...@gmail.com wrote: > I don't mind about performance but I absolutely mind about reliability, > so I was thinking about the safest setting of linux FS and postgresql I > can use. Sure, use journaling then. I do it all the time. Yours, Laurenz Albe -- Sent via pgsql-general mailing list

Re: [GENERAL] Collations and codepages

Raimo Jormakka wrote: > In Windows 7, and using PostgreSQL 9.4.5, the collation gets set to > "English_United States.1252" when > I select the "English, United States" locale in the installer. In Linux, the > collation is set to > "en_US.UTF-8". The encoding is set to UTF-8 in both instances. >

Re: [GENERAL] journaled FS and and WAL

t.dalpo...@gmail.com wrote: > two question related to the WAL. > > 1) I read in the doc that journaled FS is not important as WAL is > journaling itself. But who garantees that the WAL is written correctly? > I know that it's sequential and a partial update of WAL can be discarded > after a

Re: [GENERAL] SERIALIZABLE and INSERTs with multiple VALUES

Kevin Grittner wrote: > Sent: Tuesday, October 11, 2016 10:00 PM > To: Jason Dusek > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] SERIALIZABLE and INSERTs with multiple VALUES > > On Tue, Oct 11, 2016 at 2:29 PM, Jason Dusek wrote: > >> I notice the following

Re: [GENERAL] Restricted access on DataBases

Durumdara wrote: [...] > --- login with postgres: [...] > ALTER DEFAULT PRIVILEGES > GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER > ON TABLES > TO u_tr_db; > > login with u_tr_main: > > create table t_canyouseeme_1 (k int); > >

Re: [GENERAL] Database fixed size

Adir Shaban wrote: > Is there anyway to limit a database size? > For example, I need to create a database for user X and I don't want it to > use more than 5 GB. You can create a tablespace on a device with limited size. Then you can create the database on that tablespace. Yours, Laurenz Albe

Re: [GENERAL] Unable to create oracle_fdw (foreign data wrapper) extension

Arun Rangarajan wrote: > But when I try to create the extension, I get the following error: > > postgres=# create extension oracle_fdw; > server closed the connection unexpectedly > This probably means the server terminated abnormally > before or while processing the request. > The connection to

Re: [GENERAL] Duplicate data despite unique constraint

Jonas Tehler wrote: > We’re running Postgresql 9.4.5 on Amazon RDS. One of our tables looks > something like this: > > > CREATE TABLE users > ( > ... > email character varying(128) NOT NULL, > ... > CONSTRAINT users_email_key UNIQUE (email) > ) > > Despite this we have rows with very

Re: [GENERAL] jdbc 9.4-1208 driver for PostgreSQL 9.5?

Joek Hondius wrote: > (I hope i am on the right list) pgsql-jdbc would have been the perfect list. > jdbc.postgresql.org lists version 9.4 build 1208 as the lastest. > Is this the correct version to use with PostgreSQL 9.5 (or even 9.6-beta)? > I cannot find info on this elsewhere. Yes, you

Re: [GENERAL] Slow SQL?

Bjørn T Johansen wrote: > Thx for your suggestions. Tried to use NOT EXISTS and the query was about > half a second quicker so not > much difference... > But when I try to run the 3 queries separately, then they are very quick, 2 > barely measurable and the > third takes about 1,5 seconds. The

Re: [GENERAL] Slow SQL?

haman...@t-online.de wrote: Bjørn T Johansen wrote: >> I am trying to move a small system from Oracle to PostgreSQL and I have come >> upon a sql that runs >> really slow compared to on the Oracle database and I am not able to >> interpret why this is slow. > I have experienced that some

Re: [GENERAL] Protect a table against concurrent data changes while allowing to vacuum it

Sameer Kumar wrote: > On Wed, Jun 22, 2016 at 6:08 PM Vlad Arkhipov wrote: >> I am running PostgreSQL 9.5. >> >> CREATE TABLE t (id BIGINT NOT NULL PRIMARY KEY, name TEXT); >> >> The constraint that the data must satisfy is `there is no more than 3 >> records with the

Re: [GENERAL] Protect a table against concurrent data changes while allowing to vacuum it

Vlad Arkhipov wrote: > I have a constraint that requires a table to be locked before checking > it (i.e. no more than 2 records with the same value in the same column). > If I lock the table in the SHARE ROW EXCLUSIVE mode, any vacuuming (or > autovacuuming) process prevents me from checking the

Re: [GENERAL] Hot disable WAL archiving

Job wrote: > is there a way in Postgresql-9.5 to disable temporarily WAL archiving to > speed up pg_bulkload with > restarting database engine? You can set 'archive_command=/bin/true' and reload, then no WAL archives will be written. Make sure to perform a base backup as soon as your bulk load

Re: [GENERAL] Changelog version from 8.1.2 to 9.3.6

Yogesh Sharma wrote: > I have doubt regarding release notes of all versions. > As per release notes, below change logs are mentioned in all versions. > > "(8.3.8,8.4.1,8.2.14) Make LOAD of an already-loaded loadable module into a > no-op (Tom Lane)" > 1. What is meaning of above lines? > 2. This

Re: [GENERAL] Can SET_VARSIZE cause a memory leak?

Николай Бабаджанян wrote: > I didn't find an easy way to convert ucs-2 bytea to utf-8, so I decided to > write a C-function. Since > ucs-2 is has fixed symbol size of 2 bytes the output bytea size may differ. > > I do the following: > > bytea *result= (bytea *)

Re: [GENERAL] postgresql embedded mode

David G. Johnston wrote: > On Mon, May 23, 2016 at 6:54 AM, aluka raju wrote: >> As given in the FAQ's that postgresql cannot be embedded >> https://wiki.postgresql.org/wiki/FAQ#Can_PostgreSQL_be_embedded.3F . >> >> Is their any possibility to make it embedded. Till now

Re: [GENERAL] PG wire protocol question

Boszormenyi Zoltan wrote: > it was a long time I have read this list or written to it. > > Now, I have a question. This blog post was written about 3 years ago: > https://aphyr.com/posts/282-jepsen-postgres > > Basically, it talks about the client AND the server as a system > and if the network

Re: [GENERAL] Postgres RFC3339 datetime formatting

Jasim Mohd wrote: > Is there any way to format datetime to RFC3339Nano Eg: > 2006-01-02T15:04:05.9Z07:00 in postgres > 9.3 or 9.5? > > I tried with to_char. But there is no documentation how to handle T, Z, > +07:00, -07:00 etc. The best I can get is: SELECT to_char(current_timestamp,

Re: [GENERAL] Does the initial postgres user have a password?

dandl wrote: > I have a new 9.5 installation, Windows x64, developer only. No users have > been added, and no passwords > set. > > I can access the system: > · using pgAdmin3, without specifying a user or password > · using psql, specifying user ‘postgres’ but no password > > I

Re: [GENERAL] truncate table getting blocked

Jayadevan M wrote: > I have a python script. It opens a cursor, and sets the search_path (using > psycopg2). In case > something goes wrong in the script , a record is inserted into a table. In > that script, I am not doing > any thing else other than reading a file and publishing the lines to a

Re: [GENERAL] Error: no connection to the server

Marco Bambini wrote: > I have a multithreaded C client and sometimes I receive the "no connection to > the server" error > message. > I haven't found any documentation about it and about how to fix this issue. > > Anyone can point me to the right direction? The error message is reported in

Re: [GENERAL] How do BEGIN/COMMIT/ABORT operate in a nested SPI query?

David Bennett wrote: > > From: Albe Laurenz [mailto:laurenz.a...@wien.gv.at] > > > > > I am attempting to create a new language implementation. The language > > > is Andl (andl.org), so the handler is plandl. > > > This is a question about e

Re: [GENERAL] Enhancement Request

Melvin Davidson wrote: > On Tue, Apr 19, 2016 at 4:37 PM, Rob Brucks wrote: >> I'd like to propose two enhancements to the PostgreSQL code, but I'm not >> sure if this is the >> correct mailing list. So if it's not then please let me know where I need >> to post this.

Re: [GENERAL] How are files of tables/indexes/etc deleting?

Иван Фролков wrote: > When I drop a table, the file which contains real data will be deleted, but > only when I will commit > transaction. It seems like such job must do vacuum, but I cannot locate the > code which does the real > job. Could anybody explain in details how it works? See

Re: [GENERAL] How do BEGIN/COMMIT/ABORT operate in a nested SPI query?

da...@andl.org wrote: > I am attempting to create a new language implementation. The language is Andl > (andl.org), so the > handler is plandl. > This is a question about executing SPI queries from inside plandl. > > The documentation makes it clear that SPI allows nested queries; that in some

Re: [GENERAL] what's the exact command definition in read committed isolation level?

Jinhua Luo wrote: > The document said, "Read Committed mode starts each command with a new > snapshot that includes all transactions committed up to that instant". > > But what about the embedded commands within the outer command itself? > Do they share the same snapshot with the outer command? >

Re: [GENERAL] How to detoast a column of type BYTEAOID

da...@andl.org wrote: > I am attempting to create a new language implementation. The language is > Andl (andl.org), so the handler is plandl. > I am having a bit of trouble executing SPI queries from inside plandl. > > The particular problem arises after calling SPI_cursor_fetch() and then >

Re: [GENERAL] Postgresql 9.3.4 file system compatibility

Marllius wrote: > OCFS2 = oracle cluster file system 2 I think using OCFS2 for PostgreSQL data is a good idea if you want to be the first at something or try to discover bugs in OCFS2. Why do you want a cluster file system for PostgreSQL? You cannot have more than one server access the same

Re: [GENERAL] max_stack_depth problem though query is substantially smaller

Charles Clavadetscher wrote: > We have a process in R which reads statistical raw data from a table and > computes time series values > from them. > The time series values are in a hstore field with the date as the key and the > value as the value. > The process writes the computed value into a

Re: [GENERAL] Trying to understand page structures in PG

Rakesh Kumar wrote: >> Every row has two system columns associated with it: xmin and xmax >> >> xmin is the transaction ID that created the row, while xmax is >> the transaction ID that removed the row. >> >> So when an update takes place, xmax of the original row and xmin >> of the new row are

Re: [GENERAL] Trying to understand page structures in PG

Jeff Janes wrote: >> I am curious because of "while xmax is the transaction ID that >> *removed* the row". > > "marked for removal" would be more accurate. If the row were actually > physically removed, it would no longer have a xmax to set. Yes, thanks for the clarification. I was thinking

Re: [GENERAL] Trying to understand page structures in PG

Rakesh Kumar wrote: > I understand that when an update of say 100,000 rows are made, PG > writes the updated rows as a new row. These new rows are not visible > to any sessions except the one creating it. At commit time PG flips > something internally to make these rows visible to all. > > My Q:

Re: [GENERAL] PostgreSQL advocacy

Jernigan, Kevin wrote: >On 3/24/16, 3:09 PM, "Albe Laurenz" <laurenz.a...@wien.gv.at> wrote: >>> Disk is only a single point of failure in RAC if you configure >>> non-redundant storage. >>> In general, Oracle recommends triple mirroring to protect ag

Re: [GENERAL] PostgreSQL advocacy

Jernigan, Kevin wrote: > Disk is only a single point of failure in RAC if you configure non-redundant > storage. > In general, Oracle recommends triple mirroring to protect against disk > failures, > as they have had many experiences over the years where customers with > mirrored disks > would

Re: [GENERAL] Confusing deadlock report

Thomas Kellerer wrote: >>> The error as reported in the Postgres log file is this: >>> >>> 2016-03-12 13:51:29.305 CET [23912]: [1-1] >>> user=arthur,db=prod,app=[unknown] ERROR: deadlock detected >>> 2016-03-12 13:51:29.305 CET [23912]: [2-1] >>> user=arthur,db=prod,app=[unknown] DETAIL:

Re: [GENERAL] Confusing deadlock report

Thomas Kellerer wrote: >> Can you determine what statements were executed in these transactions before >> the deadlock? >> It was probably one of these that took the conflicting lock. > > Unfortunately not. Statement logging is not enabled on that server > (space-constrained). > > And while we

Re: [GENERAL] Confusing deadlock report

Thomas Kellerer wrote: > we have a strange (at least to me) deadlock situation which does not seem to > fall into the "usual" > deadlock category. > > The error as reported in the Postgres log file is this: > > 2016-03-12 13:51:29.305 CET [23912]: [1-1] user=arthur,db=prod,app=[unknown] >

Re: [GENERAL] psql question: aborting a "script"

John McKown wrote: > I'm likely abusing the psql program. What I have is an awk program which > reads a file and produces a > number of INSERT INTO commands. I then feed these commands into psql to > execute them. Yes, a Perl > program would be a better idea. Anyway, sometimes the commands are

Re: [GENERAL] Windows default directory for client certificates

Lupi Loop wrote: > PostgreSQL documentation at > http://www.postgresql.org/docs/9.5/static/libpq-ssl.html > says that when a client certificate is requested by a server, a windows > client psql will use by > default the credentials located at %APPDATA%\postgresql\postgresql.crt and >

Re: [GENERAL] Does RAISE EXCEPTION rollback previous commands in a stored function?

Alexander Farber wrote: > On Wed, Mar 2, 2016 at 11:18 AM, Albe Laurenz <laurenz.a...@wien.gv.at> wrote: >> You cannot use START TRANSACTION, BEGIN, SAVEPOINT, COMMIT or ROLLBACK >> inside a function. A function always runs within one transaction. >> >> Savepoin

Re: [GENERAL] Does RAISE EXCEPTION rollback previous commands in a stored function?

Alexander Farber wrote: > how to set such a savepoint inside of a stored function? > > Can I call "START TRANSACTION", and then at some point later in the same > stored function call RAISE > EXCEPTION? I realize that what I wrote must be confusing. You cannot use START TRANSACTION, BEGIN,

Re: [GENERAL] Does RAISE EXCEPTION rollback previous commands in a stored function?

Andreas Kretschmer wrote: >> Alexander Farber hat am 1. März 2016 um 19:41 >> geschrieben: >> >> >> Good evening, >> >> in PostgreSQL 9.5 does RAISE EXCEPTION reliably rollback all previous >> commands in a stored function? > > Yes. That is, unless you set a

Re: [GENERAL] Only owners can ANALYZE tables...seems overly restrictive

David G. Johnston wrote: In a production environment you don't want a user to change your table statistics. They could just set default_statistics_target to something stupid, run ANALYZE and wreck the statistics for everyone. And then come back to the DBA and complain

Re: [GENERAL] Only owners can ANALYZE tables...seems overly restrictive

David G. Johnston wrote: > On Mon, Feb 29, 2016 at 2:52 AM, Albe Laurenz <laurenz.a...@wien.gv.at> wrote: >> John R Pierce wrote: >>> analyze has arguably fewer side effects, its a performance enhancement, >>> its neither altering the schema or changing the data.

Re: [GENERAL] Only owners can ANALYZE tables...seems overly restrictive

John R Pierce wrote: > On 2/28/2016 8:58 PM, Tom Lane wrote: I should the check for whether a given user can or cannot analyze a table should be whether the user has INSERT, UPDATE, or DELETE privileges. >> By that argument, we should allow anyone with any write access to do >>

Re: [GENERAL] PosgreSQL Security Architecture

John R Pierce wrote: > On 2/12/2016 5:20 AM, Lesley Kimmel wrote: >> Thanks for the reply Laurenz. Of course the first thing that I thought >> of to prevent man-in-the-middle was SSL. However, I also like to try >> to address the issue in a way that seems to get at what they are >> intending. It

Re: [GENERAL] PosgreSQL Security Architecture

Lesley Kimmel wrote: > I'm working to secure a PosgreSQL database according to a DoD security guide. > It has many very generic > requirements that get more toward the internal architecture of the system > that wouldn't be apparent to > the average admin. I was hoping someone might have some

Re: [GENERAL] memory problem with refresh materialized view

Enrico Pirozzi wrote: > I have a new postgresql 9.5.0 installation on a new virtual server debian 8.3 > x64 with 4gb RAM, I have > compiled postgresql from source. > > When I import a dump with materialized views I see that postgres process > takes about all 4 Gb and then > I have this error >

Re: [GENERAL] PL/pgSQL debugger

Dane Foster wrote: > I googled "plpgsql debugger" and eventually ended up at > http://pgfoundry.org/projects/edb-debugger/, > where the latest release is almost 8 years old. I am aware that this is not > an EDB forum but given > that I sent them an e-mail at 9AM EST and I haven't received a

Re: [GENERAL] A motion

Regina Obe wrote: > At this point I feel we should: > > a) Move this to pgsql-advocacy [...] > Or > > b) Start a new PostgreSQL mailing list - call it - pgsql-coc. -1 While I personally feel that a code of conduct does not need to be an explicit document and is something that "happens"

Re: [GENERAL] long transfer time for binary data

Andy Colson wrote: > On 01/21/2016 03:59 PM, Johannes wrote: >> Here are some transferring measurements (from server to client) with the >> same file. >> >> scp >> +ssl -compression 1.3 sec >> +ssl +compression 4.6 sec >> >> pgadmin >> select lo_get(12345); >> -ssl 3.4 sec >> +ssl

Re: [GENERAL] Postgres and timezones

Steve Rogerson wrote: > Hi, this is wrong: > > # select to_char('2016-01-20 00:00'::timestamp at time zone 'Europe/Lisbon', > 'TZ'); > to_char > - > GMT > (1 row) > > > It should be WET, "Western European Time". Is there something I'm doing wrong? That query will always give you your

Re: [GENERAL] Function error

Sachin Srivastava wrote: > In my function the problem is that global variables defined inside the > function. > These variables are visible to functions defined inside a function. > If we move these inner functions to outside of the main function, > they will lose the visibility of the global

Re: [GENERAL] Function error

Sachin Srivastava wrote: > I am getting the below error for function, please see the bold line in > "Function code", please suggest > what I will do to correct this code. > > --- > > ERROR: syntax error at or near "*" > LINE 35: SELECT * from logError(msg text) is >

Re: [GENERAL] Code of Conduct: Is it time?

Tom Lane wrote: > In my admittedly-limited experience with dealing with such problems, > it's a lot easier to achieve positive results if you can discuss > issues in private, before people's positions harden. > > In short, I wouldn't characterize that complainant as "a troll" for > the substance

Re: [GENERAL] Postgresql INET select and default route ?

Tim Smith wrote: > Re: I am surprised that you don't expect "0.0.0.0/0" to be returned by the > first > query if you expect it to be returned by the second. > Is that an oversicht? > > Thanks for the question, but no, it wasn't an oversight, I only am > looking for 0.0.0.0/0 to be returned if

  1   2   3   4   5   6   7   8   9   10   >