Re: [GENERAL] [ADMIN] calculating table and index size

PM, Steven Chang > wrote: >> >> Hello, >> >> try pgstattuple() and pgstatindex() , I think you will figure it out. >> >> Steven >> >> 2017-04-05 16:56 GMT+08:00 Guillaume Lelarge : >>> >>> Hi, >>> >>> 2017-04-05 9

Re: [GENERAL] [ADMIN] calculating table and index size

Hi, 2017-04-05 9:44 GMT+02:00 Günce Kaya : > Hi all, > > I have some questions about calculating table and index size. > > I have a dummy table which has an integer column and its index. The table > has 140 rows and all of rows are same thats value is 2000. Table > size is 50MB and index

Re: [GENERAL] mysql_config_editor feature suggestion

2017-03-21 23:03 GMT+01:00 Tom Ekberg : > I have been working with MySQL a bit (yes, I know, heresy) and encountered > a program called mysql_config_editor. In my opinion it does a better job of > local password management than using a ~/.pgpass file. Instead of assuming > that a mode of 600 will

Re: [GENERAL] createuser: How to specify a database to connect to

2017-03-13 17:10 GMT+01:00 Tom Lane : > Guillaume Lelarge writes: > > It's not very hard to do. But I really wonder why it's not already done. > I > > fear there was a good idea, but I fail to see which one :) > > The core reason why we haven't co

Re: [GENERAL] createuser: How to specify a database to connect to

2017-03-13 16:57 GMT+01:00 Adrian Klaver : > On 03/13/2017 08:44 AM, Guillaume Lelarge wrote: > >> 2017-03-13 16:29 GMT+01:00 Schmid Andreas > <mailto:andreas.sch...@bd.so.ch>>: >> >> Hi >> >> I'm trying to add a new DB user wit

Re: [GENERAL] createuser: How to specify a database to connect to

2017-03-13 16:29 GMT+01:00 Schmid Andreas : > Hi > > I'm trying to add a new DB user with the following command from my client > machine: > createuser -h my.host.name -U mysuperusername --pwprompt newusername > > I'm getting the following message: > createuser: could not connect to database postgr

Re: [GENERAL] pg_restore to a port where nobody is listening?

2016-12-21 20:29 GMT+01:00 Daniel Westermann < daniel.westerm...@dbi-services.com>: > >> postgres@pgbox:/home/postgres/ [PG961] pg_restore -h localhost -p 5439 > -F d -C -j 2 /var/tmp/exp/ > >> > >> This runs fine but where does it connect to? Nothing is listening on > port 5439. > > >Given the la

Re: [GENERAL] How to introspect autovacuum analyze settings

2016-11-21 14:44 GMT+01:00 Benedikt Grundmann : > Hello all, > > I have a quick question. I feel like somewhere in section 23.1.6 there > should be the answer but I couldn't find it yet. Namely how can I query > the database for total number of tuples inserted, updated, or deleted since > the la

Re: [GENERAL] Feature request: separate logging

Le 18 nov. 2016 2:03 PM, "otheus uibk" a écrit : > > A glaring weakness in Postgresql for production systems is that the administrator has no way of controlling what types of logs go where. There are at least two types of logs: errors and statement logs. (I could also add: connection, syntax error

Re: [GENERAL] Exclude pg_largeobject form pg_dump

2016-11-08 6:01 GMT+01:00 amul sul : > On Tue, Nov 8, 2016 at 5:36 AM, Andreas Joseph Krogh > wrote: > > > > > > I don't see what you mean. It forces dump of Blobs if we didn't use -B > and > > if we include everything in the dump, which seems good to me. What did > you > > try that didn't work a

Re: [GENERAL] Exclude pg_largeobject form pg_dump

2016-11-07 7:06 GMT+01:00 amul sul : > On Mon, Nov 7, 2016 at 2:03 AM, Guillaume Lelarge > wrote: > >> > >> Agreed. I was afraid of that, but for some reason, didn't find that. > I'll > >> fix this. > > > > > > Fixed in v4. >

Re: [GENERAL] Exclude pg_largeobject form pg_dump

2016-11-04 9:35 GMT+01:00 Guillaume Lelarge : > Hi Amul, > > 2016-11-04 7:52 GMT+01:00 amul sul : > >> Hi Guillaume, >> >> I found following issues with this patch, sorry missed in previous post: >> >> > You don't have to be sorry for me doing shi

Re: [GENERAL] Exclude pg_largeobject form pg_dump

Hi Amul, 2016-11-04 7:52 GMT+01:00 amul sul : > Hi Guillaume, > > I found following issues with this patch, sorry missed in previous post: > > You don't have to be sorry for me doing shitty things :) > #1 : > 43 @@ -392,6 +393,10 @@ main(int argc, char **argv) > 44 dopt.output

Re: [GENERAL] Exclude pg_largeobject form pg_dump

u're right. v3 (attached) fixes this. > Apart from this concern patch looks good to me. Thanks > > Thanks. > Regards, > Amul > > The new status of this patch is: Waiting on Author > > On Mon, Oct 24, 2016 at 12:19 AM, Guillaume Lelarge > wrote: > > 2016-

Re: [GENERAL] Exclude pg_largeobject form pg_dump

2016-10-23 20:44 GMT+02:00 Guillaume Lelarge : > 2016-10-23 20:37 GMT+02:00 Andreas Joseph Krogh : > >> På søndag 23. oktober 2016 kl. 19:15:17, skrev Andreas Joseph Krogh < >> andr...@visena.com>: >> >> På søndag 23. oktober 2016 kl. 17:06:57, skrev Guillaume

Re: [GENERAL] Exclude pg_largeobject form pg_dump

2016-10-23 20:37 GMT+02:00 Andreas Joseph Krogh : > På søndag 23. oktober 2016 kl. 19:15:17, skrev Andreas Joseph Krogh < > andr...@visena.com>: > > På søndag 23. oktober 2016 kl. 17:06:57, skrev Guillaume Lelarge < > guilla...@lelarge.info>: > > 2016-03-08 21:

Re: [GENERAL] Exclude pg_largeobject form pg_dump

2016-03-08 21:06 GMT+01:00 Andreas Joseph Krogh : > På tirsdag 08. mars 2016 kl. 21:03:01, skrev David G. Johnston < > david.g.johns...@gmail.com>: > > On Tue, Mar 8, 2016 at 9:45 AM, Andreas Joseph Krogh > wrote: > >> På tirsdag 08. mars 2016 kl. 17:38:04, skrev Joshua D. Drake < >> j...@command

Re: [GENERAL] SELECT col INTO TEMP TABLE tab2 ON COMMIT DROP FROM tab1

2016-08-12 11:00 GMT+02:00 Alexander Farber : > Francisco, thanks, but - > > On Fri, Aug 12, 2016 at 10:47 AM, Francisco Olarte > wrote: > >> >> https://www.postgresql.org/docs/9.5/static/plpgsql-statement >> s.html#PLPGSQL-STATEMENTS-SQL-ONEROW >> >> > but the custom function I am trying to call

Re: [GENERAL] How sync settings or extensions in streaming replication

Le 6 juil. 2016 4:48 AM, "Michael Paquier" a écrit : > > On Wed, Jul 6, 2016 at 11:37 AM, 苏士涛 wrote: > > I am new to PostgreSQL, and i have a question about streaming > > replication. If i enable some extension on master with or without > > native code(in c), do I need to enable this extension on

Re: R: [GENERAL] Vacuum full: alternatives?

2016-06-20 17:03 GMT+02:00 Scott Mead : > > > On Mon, Jun 20, 2016 at 6:13 AM, Andreas Kretschmer < > andr...@a-kretschmer.de> wrote: > >> >> >> Am 20.06.2016 um 11:43 schrieb Job: >> >>> Hi Andreas, >>> >>> I would suggest run only autovacuum, and with time you will see a not more growing ta

Re: [GENERAL] Timestamp with timezone output

Hi, Le 25 mai 2016 4:50 PM, "Arnaud Inovia Team" a écrit : > > I have some question regarding some timezone behaviour. > > Documentation is saying: > > > When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in t

Re: [GENERAL] Allow disabling folding of unquoted identifiers to lowercase

Le 3 mai 2016 7:01 PM, "Evgeny Morozov" a écrit : > > That's an interesting idea! The client users would use is probably pgAdmin. I don't know whether pgAdmin parses the query, though. If it does then it should be relatively easy to add this. If not, I'd imagine it's not going to happen. > The pg

Re: [GENERAL] Types of blocks in the `pg_stat_statements`

2016-04-25 15:06 GMT+02:00 Victor Yegorov : > Greetings. > > I cannot get understanding about what different types of blocks means here: > http://www.postgresql.org/docs/current/interactive/pgstatstatements.html > > > I assume, that `shared` are blocks from the `shared_buffers` > and `temp` are bl

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

2016-04-19 5:41 GMT+02:00 Jinhua Luo : > > Does that mean a VOLATILE function runs in a different transaction? > > No, all statements triggered by the outer statement is within the same > transaction. If the trigger fails (without trapping the error), all > affects including changes by outer state

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

2016-02-29 15:20 GMT+01:00 Vik Fearing : > On 02/29/2016 03:15 PM, Guillaume Lelarge wrote: > > 2016-02-29 14:31 GMT+01:00 Stephen Frost > <mailto:sfr...@snowman.net>>: > > > > Realistically, ANALYZE is a background/maintenance task that > autovacuu

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

2016-02-29 14:31 GMT+01:00 Stephen Frost : > * David G. Johnston (david.g.johns...@gmail.com) wrote: > > Given the amount of damage a person with write access to a table can get > > into it seems pointless to not allow them to analyze the table after > their > > updates - since best practices woul

Re: [GENERAL] Does PostgreSQL ever create indexes on its own?

2015-11-12 23:21 GMT+01:00 Doiron, Daniel : > I’m troubleshooting a schema and found this: > > Indexes: > "pk_patient_diagnoses" PRIMARY KEY, btree (id) > "index_4341548" UNIQUE, btree (id) > "idx_patient_diagnoses_deleted" btree (deleted) > "idx_patient_diagnoses_diagnosis_type_id

Re: [GENERAL] get this warning from pgAdmin

2015-11-12 19:43 GMT+01:00 Adrian Klaver : > On 11/12/2015 10:02 AM, anj patnaik wrote: > >> I get this warning when trying to fetch data for a postgres db. Does >> this indicate a real issue? Thanks >> > > Well it means the statistics for the table are out of touch with reality. > The database wi

Re: [GENERAL] Query regarding

Le 4 nov. 2015 10:23 AM, "M Tarkeshwar Rao" a écrit : > > Hi all, > > We have started to convert some oracle sql scripts and converting them to postgres, but facing some issues to create table. > > I have some common doubts in create table script ie. > > ALTER TABLE employee > DROP PRIMARY KEY CAS

Re: [GENERAL] does pg_dump get delayed if I have a loop that does continuous insertions

2015-10-28 21:09 GMT+01:00 anj patnaik : > I was trying to achieve smallest file possible so tried the xz. Right now > the db size returned from SELECT > pg_size_pretty(pg_database_size('postgres') is 1.4 GB and the size of the > dump with xz is 2.2 GB. > > Is there a limit to the size of the data

Re: [GENERAL] question

2015-10-15 23:05 GMT+02:00 Adrian Klaver : > On 10/15/2015 01:35 PM, anj patnaik wrote: > >> Hello all, >> I will experiment with -Fc (custom). The file is already growing very >> large. >> >> I am running this: >> ./pg_dump -t RECORDER -Fc postgres | gzip > /tmp/dump >> >> Are there any other o

Re: [GENERAL] question

2015-10-15 20:40 GMT+02:00 anj patnaik : > It's a Linux machine with 8 CPUs. I don't have the other details. > > I get archive member too large for tar format. > > Is there a recommended command/options when dealing with very large > tables, aka 150K rows and half of the rows have data being inser

Re: [GENERAL] Broken primary key after backup restore.

Le 18 sept. 2015 5:23 AM, "Adrian Klaver" a écrit : > > On 09/17/2015 05:37 PM, Michael Chau wrote: >> >> 1) >> >> In Production, I have a DB2 which is replicated partially using Londiste >> from DB1. >> >> >> Well I think the above needs more explanation to help understand how the >> DB2

Re: [GENERAL] Buffers: shared hit/read to shared_buffers dependence

2015-09-09 17:06 GMT+02:00 Pavel Suderevsky : > Laurenz, Merlin, > > Thanks a lot for your explanations. > > >Even if postgres does not cache the table, the o/s will probably > > still cache it assuming it has the memory to do so. > > Could you please clarify, do I understand right that there are

Re: [GENERAL] Grouping sets, cube and rollup

Le 26 août 2015 2:06 AM, "Edson Richter" a écrit : > > Any chance to get those amazing wonderful features backported to 9.4? > No. Only bug fixes are backported. -- Guillaume.

Re: [GENERAL] Extension to rewrite queries before execution

Hi, Le 13 août 2015 9:51 PM, "Jeff Janes" a écrit : > > I am looking for an extension or a technique that will allow me to intercept a query by the exact query text, and replace that query with a different one. > > The context is running a third-party app which issues queries I have no control ov

Re: [GENERAL] How to restore

Please keep the list in your answers. Le 10 août 2015 7:51 AM, "Sachin Srivastava" a écrit : > > Dear Guillaume, > > What is the syntax of this (psql), kindy confirm. > That can be: psql -f the_file the_database > Regards, > Sachin > > On Mon, Aug 10

Re: [GENERAL] How to restore

Hi, Le 10 août 2015 7:43 AM, "Sachin Srivastava" a écrit : > > Dear Team, > > If I have taken any backup successfully through pg_dump? How can I restore this pg_dump(Sql file) without use of pg_restore. > > There is any command line option or through psql, kindly inform to me. > If it's an SQL f

Re: [GENERAL] Delete rule does not prevent truncate

2015-07-23 9:06 GMT+02:00 Tim Smith : > So tell me guys, instead of bashing away at the fact I only quoted > half a sentence or whatever, how about you answer the following : > > What exactly is was the design decision that lead to TRUNCATE being > supported by triggers but not by rules ? > > Some

Re: [GENERAL] EXCLUDE, Gist and integers

Hi, Le 16 juil. 2015 2:23 AM, "Ken Tanzer" a écrit : > > Hi. I'm looking into adding daterange exclusions to some of my tables. Following the documentation, I can do this no problem to prevent any records from overlapping: > > CREATE TEMP TABLE foo ( > client_id integer, > start_date date NO

Re: [GENERAL] Backup Method

Le 3 juil. 2015 12:31 PM, "howardn...@selestial.com" < howardn...@selestial.com> a écrit : > > > > On 03/07/2015 11:15, Guillaume Lelarge wrote: >> >> >> pg_dumpall is not more efficient than pg_dump. As far as I can tell, you're looking for PIT

Re: [GENERAL] Backup Method

Le 3 juil. 2015 12:03 PM, "howardn...@selestial.com" < howardn...@selestial.com> a écrit : > > Hi everyone, > > I am reaching the point with my database backups where the backups are taking too long, and starting to interfere with running of the system during the day. So I am looking for a bit of s

Re: [GENERAL] pg_xlog on a hot_stanby slave

say on its log files? 2015-06-16 12:41 GMT+02:00 Guillaume Lelarge : > > Le 16 juin 2015 10:57 AM, "Xavier 12" a écrit : > >> > >> Hi everyone, > >> > >> Questions about pg_xlogs again... > >> I have two Postgresql 9.1 servers in a mas

Re: [GENERAL] pg_xlog on a hot_stanby slave

Le 16 juin 2015 10:57 AM, "Xavier 12" a écrit : > > Hi everyone, > > Questions about pg_xlogs again... > I have two Postgresql 9.1 servers in a master/slave stream replication > (hot_standby). > > Psql01 (master) is backuped with Barman and pg_xlogs is correctly > purged (archive_command is used).

Re: [GENERAL] pg_start_backup does not actually allow for consistent, file-level backup

Le 8 juin 2015 2:48 PM, "otheus uibk" a écrit : > > The manual and in this mailing list, the claim is made that consistent, file-level backups may be made by bracketing the file-copy operation with the postgresql pg_start_backup and pg_stop_backup operations. Many people including myself have fou

Re: [GENERAL] Is my standby fully connected?

When I ran -> psql -c "SELECT pg_current_xlog_location()" > -h192.168.0.10 (primary host) > Well, you can't execute it on a slave. You can use pg_last_xlog_receive_location() and pg_last_xlog_replay_location() on a slave. > > On Mon, Jun 1, 2015 at 2:

Re: [GENERAL] Is my standby fully connected?

Hi, 2015-06-01 19:57 GMT+02:00 Ivann Ruiz : > I just setup my databases and got this LOG: > > LOG: database system was shut down in recovery at 2015-06-01 13:31:21 EDT > LOG: entering standby mode > LOG: redo starts at 0/67000278 > LOG: consistent recovery state reached at 0/67000350 > LOG:

Re: [GENERAL] Strange replication problem - segment restored from archive but still requested from master

2015-05-25 15:15 GMT+02:00 Piotr Gasidło : > 2015-05-25 11:30 GMT+02:00 Guillaume Lelarge : > > >> I currently have wal_keep_segments set to 0. > >> Setting this to higher value will help? As I understand: master won't > >> delete segment and could stre

Re: [GENERAL] Strange replication problem - segment restored from archive but still requested from master

2015-05-22 18:36 GMT+02:00 Piotr Gasidło : > 2015-05-22 6:55 GMT+02:00 Fujii Masao : > > > > This problem happens when WAL record is stored in separate two WAL files > and > > there is no valid latter WAL file in the standby. In your case, the > former file > > is 00044C4D0090 and the

Re: [GENERAL] schedulers

2015-05-03 16:55 GMT+02:00 Pavel Stehule : > > > 2015-05-03 16:40 GMT+02:00 Guillaume Lelarge : > >> 2015-05-03 16:10 GMT+02:00 Yves Dorfsman : >> >>> >>> I just want to confirm that currently there is no scheduler that isn't >>> dependent

Re: [GENERAL] schedulers

2015-05-03 16:10 GMT+02:00 Yves Dorfsman : > > I just want to confirm that currently there is no scheduler that isn't > dependent on a crontab (like PgAgent), that could be run entirely from > within > PostgreSQL only. > > Anybody knows of one? > > There's none. -- Guillaume. http://blog.guil

Re: [GENERAL] Where does vacuum FULL write temp-files?

2015-04-15 10:46 GMT+02:00 Andreas Joseph Krogh : > På onsdag 15. april 2015 kl. 04:34:31, skrev Venkata Balaji N < > nag1...@gmail.com>: > > > >> I'm planning to vacuum FULL a pg_largeobject relation (after vacuumlo'ing >> it). The relation is 300GB large so I'm concerned the operation will write

Re: [GENERAL] Hot standby problems: consistent state not reached, no connection to master server.

Le 12 avr. 2015 16:50, "Ilya Ashchepkov" a écrit : > > Hello. > > I'm setting up hot standby slave. > It recovers from wal archive files, but I can't connect to it: > $ psql > psql: FATAL: the database system is starting up > > On master: > # select name,setting from pg_settings where name like '

Re: [GENERAL] How to hide stored procedure's bodies from specific user

2015-02-14 14:07 GMT+01:00 Berend Tober : > Saimon Lim wrote: > >> Thanks for your help >> >> I want to restrict some postgres users as much as possible and allow >> them to execute a few my own stored procedures only. >> > > Create the function that you want restrict access to in a separate > 'pr

Re: [GENERAL] Cluster seems broken after pg_basebackup

someone figuring out > a solution to get my cluster up and running again. > > PS. I was thinking of reinstalling PGSQL over my current install but > keeping my PGDATA. I've done it in the past for fixing problems with > starting the service and it worked. What do you think? > >

Re: [GENERAL] Cluster seems broken after pg_basebackup

Le 6 févr. 2015 17:31, "Adrian Klaver" a écrit : > > On 02/06/2015 05:03 AM, Guillaume Drolet wrote: >> >> Hi, >> >> Yesterday I ran a pg_basebackup of my cluster. Since it has completed, >> my cluster doesn't work properly. I tried restarting the computer (or >> service) a few times but I always

Re: [GENERAL] vacuum full post 9.0 - reindex needed?

Hi, 2015-01-28 21:39 GMT+01:00 Anand Kumar, Karthik < karthik.anandku...@classmates.com>: > I haven’t been able to find clear information online about what happens > with existing indexes on a postgres post-9.0 database (we specifically use > 9.3) after a vacuum full. > > There is a lot of info

Re: [GENERAL] Information regarding Table-Locks

Le 16 janv. 2015 06:27, "sri harsha" a écrit : > > Hi John. > >What if my query was INSERT INTO Postgres_Table_A SELECT * FROM Postgres_Table_B ?? What are the locks given for the two tables ?? > If they are both regular tables, they can insert at the same time. But you can't rename a take wh

Re: [GENERAL] SSO Windows-to-unix

Le 14 janv. 2015 05:22, "Jeremy Palmer" a écrit : > > I think PgAdmin is just a client that uses libpq and does not specifically help with SSO. > You're definitely right about that. > > > From: Raghu Ram [mailto:raghuchenn...@gmail.com] > Sent: Tuesday, 13 January 2015 10:22 p.m. > To: Jeremy Pa

Re: [GENERAL] Can't drop a view ("view does not exist") that has an entry in "INFORMATION_SCHEMA.views"

Le 17 nov. 2014 22:49, "Klaus Hofeditz ]project-open[" < klaus.hofed...@project-open.com> a écrit : > > Hi guys, > I need to drop a view in order to alter a type of a column: numeric(12,1) -> numeric(12,2): > > ERROR: cannot alter type of a column used by a view or rule DETAIL: rule _RETURN on v

Re: [GENERAL] Tablespace limit feature

Le 4 nov. 2014 17:59, "Stephen Frost" a écrit : > > * Guillaume Lelarge (guilla...@lelarge.info) wrote: > > Le 4 nov. 2014 16:29, "Stephen Frost" a écrit : > > > Having quotas and limits for users is something which I've wanted for a > >

Re: [GENERAL] Tablespace limit feature

's no way yet to do this with PostgreSQL. You might get something like this with filesystem quotas. And it's not on PostgreSQL roadmap, because there's no such thing. I don't know anyone working on this. > > Thanks > > El Martes 4 de noviembre de 2014 11:52, G

Re: [GENERAL] Tablespace limit feature

Le 4 nov. 2014 16:29, "Stephen Frost" a écrit : > > * Alejandro Carrillo (faster...@yahoo.es) wrote: > > Can PostgreSQL's tablespace limit space in MB? Or exists another way to limit space in a table of a tablespace?? > > You can set up quotas on the underlying filesystem, but that will limit > th

Re: [GENERAL] How to start several PostgreSQL clusters at boot time on a Debian OS

Hi, 2014-10-15 12:18 GMT+02:00 Léa Massiot : > Hello and thank you for reading my post. > > My question is about starting PostgreSQL clusters at boot time. > > The OS is Debian Wheezy. > I have installed PostgreSQL from the sources (postgresql-9.3.5.tar.gz) at > http://www.postgresql.org/ftp/sour

Re: [GENERAL] Importing large object (with lo_import) to table in separate tablespaces takes up lots of space in $PGDATA

Hi, Le 9 oct. 2014 01:31, "Andreas Joseph Krogh" a écrit : > > Hi all. > > I'm having a database, called "apeland", which at first (when created) was in the default-tablespace (in $PGDATA), then I moved it with the commands: > > # create tablespace apeland location '/home/andreak/programs/postgre

Re: [GENERAL] Performance issue with postgres_fdw

Le 29 août 2014 18:13, "Emmanuel Medernach" a écrit : > > Hello, > > I use Postgres version 9.3.5 and spot a performance issue > with postgres_fdw. > > I have a table object_003_xyz with 275000 lines and is > exported to the master node as master_object_003_xyz. > > ( The following query

Re: [GENERAL] change the value of "unix_socket_directories" , must used "-h /xx/xx" to use the Unix domain socket

Le 16 août 2014 17:44, "lin" a écrit : > > Hi all, > I change the value of "unix_socket_directories" in postgresql.conf , then restart the database, but it cannot connect the database used like this > "psql -d postgres -p 5432" , it must given the parameter " -h /xx/xx" to use the Unix doma

Re: [GENERAL] Questions on dynamic execution and sqlca

2014-08-07 7:24 GMT+02:00 David Johnston : > >> > > - What are the differences among PL/SQL, PL/PGSQL and pgScript. >> > >> > The first two are languages you write functions in. pgScript is simply >> an >> > informal way to group a series of statements together and have them >> execute >> > with

Re: [GENERAL] Questions on dynamic execution and sqlca

Le 6 août 2014 18:47, "David G Johnston" a écrit : > > Bill Epstein wrote > > I've tried a variety of ways based on the on-line docs I've seen, but I > > always get a syntax error on EXEC when I use only the line EXEC statement > > You likely need to use "EXECUTE" in PostgreSQL > > > >INFO:

Re: [GENERAL] Calltips in pgAdmin SQL Editor

t. > > Apologies for posting in wrong list, would be great if Administrator could > move the message thread to appropriate group. > > That won't happen. This isn't a web forum :) > > Thanks, > Klo > > > On Sun, Jul 27, 2014 at 1:43 PM, Guillaume Lelarg

Re: [GENERAL] Calltips in pgAdmin SQL Editor

Hi, 2014-07-27 8:55 GMT+02:00 klo uo : > Hi, > > I believe SQL Editor in pgAdmin wx GUI is Scintilla. > Scintilla supports calltips (as in SciTE with *.api files). > > If above is true, is there a way to make SQL Editor support calltips on > user defined commands? > > pgAdmin uses scintilla on th

Re: [GENERAL] What query currently running within function

Hi, 2014-07-22 11:36 GMT+02:00 Rebecca Clarke : > Hi all, > > Presently I'm executing a function that runs many queries within it. > > select * from _myfunction(); > > Is there a way to see what query it is up to within the function? > Unfortunately, no. Even with the latest release. pg_stat_act

Re: [GENERAL] checkpoint

2014-07-10 20:56 GMT+02:00 Yves Dorfsman : > > Hi, > > If I run checkpoint from psql, is it applied to all the databases? > > What if I do it though an API? When connecting with psycopg2, I'm forced to > specify a database name, if I use "dbname=postgres", and execute > "checkpoint;", is it applie

Re: [GENERAL] Filtering queries by IP

On Mon, 2014-01-20 at 10:21 -0300, Leonardo M. Ramé wrote: > Hi, I'm trying to find the cause of slow performance on some screens of > an application. To do that, I would like to be able to log all the > queries made by an specific IP addres, is this possible?. > Not by default, but you can use t

Re: [GENERAL] excution time for plpgsql function and subfunction

On Fri, 2014-01-10 at 10:10 +0100, Rémi Cura wrote: > Hey List, > kind of a simple question : > > I'm using the postgis_topology extension, > and I'm trying to figure where the slowness comes from when importing data. > > It involves plpgsql function calling other plpgsql functions, insert, > upd

Re: [GENERAL] streaming replication not working

On Tue, 2013-09-24 at 08:59 -0500, John DeSoi wrote: > On Sep 24, 2013, at 5:48 AM, Albe Laurenz wrote: > > >> 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 paramete

Re: [GENERAL] Howto see template objects in PGAdmin3

On Tue, 2013-02-12 at 15:32 -0500, James B. Byrne wrote: > PG-9.2 > PGAdmin3-1.16.0 > > I have run into an old problem wherein the postgres user has ownership > of the PSQL language extension in a newly created database. I wish to > remove this extension from template1 as the database is automati

Re: [GENERAL] Visual query builder for PosgreSQL?

On Fri, 2013-02-08 at 11:37 +, Russell Keane wrote: > > I'm looking for some good visual query builder which can be used by > > non-tech people for some ETL tasks. Do you have any recommendation? > > > > Now, we're moving our data from Excel to PostgreSQL to deal with large > > amount of data

Re: [GENERAL] Optimizing select count query which often takes over 10 seconds

On Fri, 2013-01-25 at 16:13 +0100, Alexander Farber wrote: > Hi - > > On Thu, Jan 24, 2013 at 8:12 PM, Guillaume Lelarge > wrote: > > You should better create an index on pref_money(yw, money). It could > > help you get rid of the seqscan and sort operations. > &

Re: [GENERAL] Optimizing select count query which often takes over 10 seconds

On Thu, 2013-01-24 at 15:45 +0100, Alexander Farber wrote: > Hello - > > On Thu, Jan 24, 2013 at 3:39 PM, Alban Hertroys wrote: > > On 24 January 2013 10:57, Alexander Farber > > wrote: > >> > >> # explain analyze select count(id) from ( > >> select id, > >>row_nu

Re: [GENERAL] DB alias ?

On Thu, 2013-01-24 at 09:01 +0530, Shridhar Daithankar wrote: > On Wednesday, January 23, 2013 09:39:43 PM Gauthier, Dave wrote: > > Then someone who wants to look at old JAN data will have the same problem > > :-( > > > > If I recall, Oracle enables something like this. Multiple tnsfilenames (or

Re: [GENERAL] Bug in PgAdmin 1.16.1

On Sun, 2013-01-13 at 01:49 -0200, Edson Richter wrote: > Don't know if pgAdmin maintainer keep an eye in this list, but here I go: > > - Using pgAdmin 1.16.1 in Windows 7 x64 downloaded today > > 1) You alter a role, adding any information you want: when the > properties page is open, pgAdmin a

Re: [GENERAL] XML Schema for PostgreSQL database

On Sun, 2012-12-16 at 22:25 -0200, Edson Richter wrote: > Em 16/12/2012 20:27, Guillaume Lelarge escreveu: > > On Fri, 2012-12-14 at 14:17 -0200, Edson Richter wrote: > >> Em 14/12/2012 12:21, Merlin Moncure escreveu: > >>> On Thu, Dec 13, 2012 at 5:52 PM, Edson Richt

Re: [GENERAL] XML Schema for PostgreSQL database

On Fri, 2012-12-14 at 14:17 -0200, Edson Richter wrote: > Em 14/12/2012 12:21, Merlin Moncure escreveu: > > On Thu, Dec 13, 2012 at 5:52 PM, Edson Richter > > wrote: > >> Em 13/12/2012 20:10, Merlin Moncure escreveu: > >>[...] > > > *) diagram output should be standard html (only) without requir

Re: [GENERAL] PgAdmin question (maybe off topic)

On Tue, 2012-12-11 at 18:13 -0200, Edson Richter wrote: > Dear list, > > When using PgAdmin on Windows 7 (64bit), sometimes I can manage indexes, > sometimes cannot. > Example (can manage indexes normally): > > > > > Cannot manage indexes at all: > > > > Must be something related to the ta

Re: [GENERAL] Bug in postgres 9.2 installation: pg_archivecleanup: command not found

On Sun, 2012-12-02 at 08:09 -0800, rahul143 wrote: > I have installed postgres 9.2 on ubuntu 12.04 like this: > > sudo add-apt-repository ppa:pitti/postgresql > sudo apt-get update > sudo apt-get install postgresql-9.2 > > However after this, i get the following error: > > 2012-11-12 17:49:38 GM

Re: [GENERAL] alter sequence

On Sun, 2012-12-02 at 11:42 +, Jasen Betts wrote: > On 2012-11-24, Peter Kroon wrote: > > --f46d04389321c8f47d04cf3c0f32 > > Content-Type: text/plain; charset=ISO-8859-1 > > > > ALTER SEQUENCE (select pg_get_serial_sequence('table', 'id')) RESTART WITH > > 1; > > > The query fails: > > ALTER

Re: [GENERAL] Revoke "drop database" even for superusers?

On Sun, 2012-12-02 at 08:09 -0200, Edson Richter wrote: > Em 02/12/2012 07:53, Guillaume Lelarge escreveu: > > On Sun, 2012-12-02 at 10:32 +0530, Pavan Deolasee wrote: > >> On Sun, Dec 2, 2012 at 7:53 AM, Edson Richter > >> wrote: > >> > >>&

Re: [GENERAL] Revoke "drop database" even for superusers?

On Sun, 2012-12-02 at 10:32 +0530, Pavan Deolasee wrote: > On Sun, Dec 2, 2012 at 7:53 AM, Edson Richter wrote: > > > Em 01/12/2012 22:22, Chris Angelico escreveu: > > > > On Sun, Dec 2, 2012 at 10:20 AM, Edson Richter > >> wrote: > >> > >>> I've put both files in ~/deny_drop folder, and execute

Re: [GENERAL] Getting show results into a table

On Wed, 2012-11-28 at 12:38 -0600, Little, Douglas wrote: > Is there a way in sql to get the results of the show all command into a table? > SELECT name, setting, short_desc FROM pg_settings > I'm expecting something like > Insert into Config_history as select * from (show all); > INSERT INTO

Re: [GENERAL] Adding Default Privileges to a schema for a role

On Mon, 2012-11-26 at 18:56 +, Mark Volz wrote: > Hello, > > I created a role called Workers. I am able edit the privileges for the > Workers role in the public schema of a database. I also would like to edit > the default privileges of the schema as well so that the Workers group will > a

Re: [GENERAL] Revoke "drop database" even for superusers?

On Fri, 2012-11-23 at 23:56 -0200, Edson Richter wrote: > Wordeful! > > Guillaume, Thanks. > > I"ll give a try for few weeks in the development and test databases > before put in production. > Make sure you test it thoroughly. As I said, it's more an example code, than a production-ready code.

Re: [GENERAL] Revoke "drop database" even for superusers?

On Fri, 2012-11-09 at 09:19 -0200, Edson Richter wrote: > I've a bunch of databases that cannot be dropped in any case. > > I was wondering if it is possible to revoke "drop database" permission > for all users, in order that even superuser, if he wishes to drop a > database, he will need first

Re: [GENERAL] pg_stats in 9.2

On Wed, 2012-10-17 at 11:57 -0400, Tom Lane wrote: > Guillaume Lelarge writes: > > Anyone care to explain all this to me? :) > > Try the stats-slot type specifications in > src/include/catalog/pg_statistic.h > Oh, OK, got it. The three more values are, in order, the s

[GENERAL] pg_stats in 9.2

Hi, I try to understand the new columns in pg_stats in 9.2, but I'm kinda puzzled by the results. Here is the test case I'm working on: CREATE TABLE t1(c1 integer, c2 integer[]); INSERT INTO t1 select 1, '{4}'; INSERT INTO t1 select 2, '{5}'; INSERT INTO t1 select 3, '{6}'; ANALYZE t1; SELECT *

Re: [GENERAL] Question about granting permissions

On Sun, 2012-08-26 at 13:10 +0200, Matvey Teplov wrote: > Hi, > > Sorry to bother with the stupid question guys - I'm new to the Postgres. > I'm having issue allowing user to access the database - the user is > not allowed to access the data. I do the following: > 1) grant all on database testdb

Re: [GENERAL] postgres maintenance db

On Thu, 2012-07-26 at 15:07 -0700, hartrc wrote: > I'm using postgres 9.1.4 on Suse Linux Enterprise Server 11. > After successful installation I by default have one database installed > called postgres. > > I'm starting the process of migrating some database schemas off Oracle and > mysql onto po

Re: [GENERAL] Roles with empty password (probably bug in libpq and in psql as well).

On Tue, 2012-07-24 at 17:36 +0400, Dmitriy Igrishin wrote: > Hey Guillaume, > > 2012/7/24 Guillaume Lelarge > On Tue, 2012-07-24 at 16:41 +0400, Dmitriy Igrishin wrote: > > Hey all, > > > > According to > http://www.p

Re: [GENERAL] Roles with empty password (probably bug in libpq and in psql as well).

On Tue, 2012-07-24 at 16:41 +0400, Dmitriy Igrishin wrote: > Hey all, > > According to http://www.postgresql.org/docs/9.2/static/sql-alterrole.html > > A query: > ALTER ROLE davide WITH PASSWORD NULL; > removes a role's password. > > But it's impossible to pass empty (NULL) password to the backe

Re: [GENERAL] meaning of "waiting" column in pg_stat_activity?

On Mon, 2012-07-23 at 16:13 -0400, Chris Curvey wrote: > Just wondering...if a query is "waiting", what is it waiting for? Locks. > Is > this only for locks, or can it be for disk, or a memory buffer? Only locks. > (If > there is a description of the meaning of things in pg_stat_activity >

  1   2   3   4   >