Re: [GENERAL] psql sqlstate return code access

2015-03-16 Thread Luca Ferrari
On Mon, Mar 16, 2015 at 4:23 PM, Little, Doug C wrote: > insert into x select … from y; > > > > insert into log(message, code,timestamp) values('insert into > x',:SQLSTATE,now()); > > I'm pretty sure you have to wrap it into a plpgsql function: http://www.postgresql.org/docs/current/static/plpgs

Re: [GENERAL] Oracle baseline/baseplan/outplan in Postgres

2015-08-06 Thread Luca Ferrari
On Thu, Aug 6, 2015 at 3:52 PM, Uriy Zhuravlev wrote: > Hello all. > What do you think about adding functionality baseplan/outplan in Postgres? Who > needs it in postgres? I suspect they will not be introduced for the same reason of query hints. I trust the optimizer to do the right choice, or at

Re: [GENERAL] template1 being accessed

2016-04-04 Thread Luca Ferrari
(sorry, back to the list) On Tue, Apr 5, 2016 at 6:11 AM, John R Pierce wrote: > its also possible some management software might use it as a default place > to connect so they can get a list of databases or whatever . This is probably the most common case for continuos access to template1. Lu

[GENERAL] unnest-like pg_stats.most_common_values and pg_stats.most_common_freqs

2017-11-17 Thread Luca Ferrari
Hi all, maybe this is trivial, but I need an hint on a way to see a table form of the MCVs and MCFs out of pg_stats with a query. Is it possible to get a set of rows each with a most common value on one column and the corresponding column on the the other? (assuming I can cast the array of MCVs to

Re: [GENERAL] unnest-like pg_stats.most_common_values and pg_stats.most_common_freqs

2017-11-20 Thread Luca Ferrari
On Fri, Nov 17, 2017 at 9:41 PM, Justin Pryzby wrote: > I think you want something like this ? > > postgres=# SELECT schemaname, tablename, attname, > unnest(histogram_bounds::text::text[]), histogram_bounds FROM pg_stats LIMIT > 9; > pg_catalog | pg_pltemplate | tmplname| plperl

Re: [GENERAL] query optimizer

2007-07-19 Thread Luca Ferrari
On Tuesday 17 April 2007 Tom Lane's cat, walking on the keyboard, wrote: > "jungmin shin" <[EMAIL PROTECTED]> writes: > > As I see the documentation of postgres, postgres use genetic algorithm > > for query optimization rather than system R optimizer. right? > > Only for queries with more than geqo

Re: [GENERAL] query optimizer

2007-07-19 Thread Luca Ferrari
On Thursday 19 July 2007 Tom Lane's cat, walking on the keyboard, wrote: > http://developer.postgresql.org/pgdocs/postgres/overview.html > (particularly 42.5) I have already read this, thanks. > > src/backend/optimizer/README I've read this yesterday, very interesting, but I'm looking for someth

Re: [GENERAL] query optimizer

2007-07-24 Thread Luca Ferrari
On Saturday 21 July 2007 Tom Lane's cat, walking on the keyboard, wrote: > Beyond that, the GEQO chapter provides several references, and IMHO > you should not be all that resistant to looking into the source code. > Even if you don't read C well, many of the files provide a wealth of > info in the

[GENERAL] a few questions (and doubts) about xid

2007-07-24 Thread Luca Ferrari
HI all, apologize me for my stupid questions but I'd like to better understand how mvcc works. Now, each tuple has xmin (insert xid) and xmax (delete/update xid). In short each transaction with xmin<=xid<=xmax can see such tuple, otherwise it cannot (of course beeing xmin and xmax different tran

Re: [GENERAL] a few questions (and doubts) about xid

2007-07-25 Thread Luca Ferrari
Thanks Gregory, thanks Simon. I'm trying to read the tqual.c source file to better understand. On Wednesday 25 July 2007 Gregory Stark's cat, walking on the keyboard, wrote: > If you really want to understand how snapshots work at this level you could > read (slowly -- it's pretty dense stuff) thr

Re: [GENERAL] granting acces to an external client

2007-07-25 Thread Luca Ferrari
On Wednesday 25 July 2007 Sorin N. Ciolofan's cat, walking on the keyboard, wrote: > Hello! > > I'd like to ask you what line should be added in pg_hba.conf file in order > to grant access to a user with ip > 139.100.99.98 to a db named "myDB" with user "scott" with password > "mikepwd"? > This s

Re: [GENERAL] a few questions (and doubts) about xid

2007-07-25 Thread Luca Ferrari
On Wednesday 25 July 2007 Gregory Stark's cat, walking on the keyboard, wrote: > If you really want to understand how snapshots work at this level you could > read (slowly -- it's pretty dense stuff) through > src/backend/utils/time/tqual.c:HeapTupleSatisfiesMVCC() Ok, I need a little hint here:

Re: [GENERAL] a few questions (and doubts) about xid

2007-07-27 Thread Luca Ferrari
Thanks all for your comments. Just another little hint here (sorry for trivial questions): if (TransactionIdIsCurrentTransactionId(HeapTupleHeaderGetXmax(tuple))) { if (tuple->t_infomask & HEAP_IS_LOCKED) return true; if (Hea

Re: [GENERAL] [pgsql-advocacy] European users mailing list

2007-07-30 Thread Luca Ferrari
On Monday 30 July 2007 Robert Treat's cat, walking on the keyboard, wrote: > *shrug* I wasn't there either, but seems all the emails I have seen > reffered to it as the "European PostgreSQL Users Group", so I expected it > to look more like other users groups, ie. [EMAIL PROTECTED] This mailing l

Re: [GENERAL] PG Admin

2007-07-31 Thread Luca Ferrari
On Tuesday 31 July 2007 Bob Pawley's cat, walking on the keyboard, wrote: > Can anyone tell me why a table developed through the PG Admin interface > isn't found by SQL when accessing it through the SQL interface?? Maybe it is a problem of case-sensitive names? Check in the table definition of pg

Re: [GENERAL] a few questions (and doubts) about xid

2007-08-01 Thread Luca Ferrari
On Friday 27 July 2007 Alvaro Herrera's cat, walking on the keyboard, wrote: > Consider an open cursor; you open it and leave it there. Then you > delete something from the table. Then you read from the cursor. The > deleted row must be in the cursor. Thanks fot these details. Now a few other q

Re: [GENERAL] a few questions (and doubts) about xid

2007-08-01 Thread Luca Ferrari
On Wednesday 1 August 2007 Gregory Stark's cat, walking on the keyboard, wrote: > You're right, the index contains pointers to *every* version of the tuple. > So in a regular SELECT statement you don't need to look at the update chain > at all. > > The main use of the update chain is when you wan

[GENERAL] pg_class.relfilenode for large tables

2007-08-20 Thread Luca Ferrari
Hi all, when a table becomes large a new file on disk is created. Such file has the name compound by the pg_class.relfilenode attribute and an incremental index. However it seems to me this does not appears in the pg_class table. Is there any place where this extra file appears? As an example:

Re: [GENERAL] Join between tables of two or more databases

2007-10-31 Thread Luca Ferrari
On Wednesday 31 October 2007 T.J. Adami's cat, walking on the keyboard, wrote: > The question is: can I do this using remote database servers > (different hosts)? If does not, can I do it at least on local > databases on the same server? I guess the dblink module could help you. Luca ---

Re: [GENERAL] hibernate + postgresql ?

2007-11-29 Thread Luca Ferrari
On Thursday 29 November 2007 Joshua D. Drake's cat, walking on the keyboard, wrote: > If you are not lazy you can push outside the standard hibernate methods > and produce very usable code but then you have to wonder why you have > hibernate there at all. What do you mean with this? I think ORM c

Re: [GENERAL] hibernate + postgresql ?

2007-12-05 Thread Luca Ferrari
On Saturday 1 December 2007 David Fetter's cat, walking on the keyboard, wrote: > You'd only think so if you hadn't actually seen these things in > action. They save no time because of the silly, unreasonable > assumptions underlying them, which in turn cause people to do silly, > unreasonable th

[GENERAL] help understanding explain output

2011-02-15 Thread Luca Ferrari
Hello, I've got a doubt about partial indexes and the path chosen by the optimizer. Consider this simple scenario: CREATE TABLE p( pk serial NOT NULL , val2 text, val1 text, b boolean, PRIMARY KEY (pk) ); INSERT INTO p(pk, val1, val2, b) VALUES( generate_series(1,100), 'val1b', 'val2b', true

[GENERAL] 9.3.5 failing to compile with dtrace on FreeBSD 10.1

2014-12-16 Thread Luca Ferrari
Hi all, I'm trying to compile 9.3.5 from ports on FreeBSD 10.1-release with dtrace enabled. It was a long time ago I looked at dtrace + PostgreSQL + FreeBSD (see [1], [2]), so I'm not updated on how far it got. The problem I have in compilation is as follows: gmake[2]: Entering directory '/mnt/ada

Re: [GENERAL] 9.3.5 failing to compile with dtrace on FreeBSD 10.1

2014-12-16 Thread Luca Ferrari
On Tue, Dec 16, 2014 at 4:06 PM, Tom Lane wrote: > If you want to push on this I think you'll need to find a BSD dtrace > expert. You shouldn't need to show him/her much except the above > dtrace invocation and the probes.d file. > I've filled a bug report and I'll report back here if I get any

Re: [GENERAL] 9.3.5 failing to compile with dtrace on FreeBSD 10.1

2015-02-23 Thread Luca Ferrari
Thanks, I'm going to try this solution and report back also to the bug tracker I opened time ago: https://bugs.freebsd.org/bugzilla/show_bug.cgi?id=196053 Luca On Sat, Jan 24, 2015 at 8:53 AM, Keith Fiske wrote: > Just wanted to thank Lacey for the assistance. I set up my first BSD server > rece

Re: [GENERAL] BLOB updates -> database size explodes

2013-05-30 Thread Luca Ferrari
On Thu, May 30, 2013 at 12:49 AM, Stephen Scheck wrote: > If this hypothesis is correct, doing a vacuum should free up dead pages and > your size expectations should be more accurate. And if that's the case > putting more intelligence into the application could mitigate some of the > update growt

Re: [GENERAL] databse version

2013-06-11 Thread Luca Ferrari
On Mon, Jun 10, 2013 at 9:52 AM, Philipp Kraus wrote: > Hello, > > I'm creating a database and I have got a table with a "version" field. Not sure, but if the version field is something like the version row indicator used by some frameworks (e.g., Hibernate), then you are going to place the updat

Re: [GENERAL] What is the difference between cmin and cmax

2013-07-01 Thread Luca Ferrari
On Tue, Jul 2, 2013 at 5:19 AM, 高健 wrote: > Hello: > I looked into the source code, and I think I now understand it: > cmin and cmax are same! The documentation is too old now. Yes, you figured it out. For short: cmin and cmax are overlapped fields and are used within the same transaction to iden

Re: [GENERAL] Analyzing last run query in psql

2013-07-03 Thread Luca Ferrari
On Wed, Jul 3, 2013 at 7:05 AM, Joe Van Dyk wrote: > I frequently need to analyze the last query in psql: > select * from table where id = 1; > explain analyze select * from table where id = 1; > > It would be nice to be able to do this: > explain analyze $LAST > > (or can I do somethi

Re: [GENERAL] query on query

2013-07-04 Thread Luca Ferrari
On Fri, Jul 5, 2013 at 5:09 AM, Jayadevan M wrote: > > So each student may get counted many times, someone with 99 will be counted > 10 times. Possible to do this with a fat query? The table will have many > thousands of records. > Not sure I got the point, but I guess this is a good candidate

Re: [GENERAL]

2013-07-14 Thread Luca Ferrari
On Fri, Jul 12, 2013 at 1:23 PM, Vincenzo Romano wrote: > Hi all > I'm making some experiments with table archiving and I'd like to > "replace" a full table F with an empty one E. > In order to do this I see only one way: > > ALTER TABLE F RENAME TO T; > ALTER TABLE E RENAME TO F; > ALTER TABLE T

Re: [GENERAL]

2013-07-14 Thread Luca Ferrari
On Sun, Jul 14, 2013 at 8:36 PM, Vincenzo Romano wrote: > I am only concerned about how late is done the binding between a table > name and the actual OID for other functions, views and triggers. Well, it should work like this: once the parser decides that a query looks good, it seaches for the

Re: [GENERAL]

2013-07-15 Thread Luca Ferrari
On Mon, Jul 15, 2013 at 8:33 AM, Vincenzo Romano wrote: > The alternative is to do things the "good ol' way" by DELETING+INSERTING > (http://tapoueh.org/blog/2013/07/05-archiving-data-fast.html) > Where I'd fear for longer LOCKs. I don't know if this is an option for your case study, but you co

Re: [GENERAL] last_vacuum field is not updating

2013-07-16 Thread Luca Ferrari
On Mon, Jul 15, 2013 at 6:43 PM, Giuseppe Broccolo wrote: > Are you sure you are the table's owner? It should not be a permission problem: it works even after a revoke all on 9.2.4. Interestingly also the autovacuum is really old. Have you tried to do a simple vacuum? From the documentation (ht

Re: [GENERAL] last_vacuum field is not updating

2013-07-16 Thread Luca Ferrari
On Tue, Jul 16, 2013 at 3:22 PM, AI Rumman wrote: > Yes, I am sure that I am looking for the same table. > What if you analyze the table? Does the column on the stats get updated? Have you tested such behavior against another (even dummy) table? Luca -- Sent via pgsql-general mailing list (pg

Re: [GENERAL] Parameter for query

2013-07-16 Thread Luca Ferrari
On Wed, Jul 17, 2013 at 6:25 AM, Robert James wrote: > Is there any way to set a variable or parameter for a query? > > I have a long query where a certain variable needs to be easy to > change. I'd like to do something like: > > threshold = 10.3 > > SELECT... WHERE x > $threshold... AND y * 1.3

Re: [GENERAL] Driver Question

2013-07-17 Thread Luca Ferrari
On Tue, Jul 16, 2013 at 5:36 PM, Corbett, James wrote: > Hello all: > > My first official message so please be gentle with me. > > I’m attempting to make a new JDBC Connection Profile via my Eclipse IDE. > Apparently it’s looking for the following jar in the driver wizard: > > postgresql-8.1-404.j

Re: [GENERAL] Question re contribs of Julian Assange

2013-07-17 Thread Luca Ferrari
On Wed, Jul 17, 2013 at 10:15 AM, ERR ORR wrote: > I noticed on the postgresql website that a certain Julian Assange is > mentioned among the contributors to Postgresql. > > Out of curiosity, could anybody post in short what exactly he contributed to > the DB? http://lmgtfy.com/?q=julian+assange

Re: [GENERAL] pgAdmin for ubuntu

2013-07-17 Thread Luca Ferrari
On Wed, Jul 17, 2013 at 4:02 PM, Muhammad Bashir Al-Noimi wrote: > Failed to fetch > bzip2:/var/lib/apt/lists/partial/archive.ubuntu.com_ubuntu_dists_quantal-updates_main_binary-amd64_Packages > Hash Sum mismatch I guess this is the problem. It happened to me sometimes when working behind a pro

Re: [GENERAL] Different transaction log for database/schema

2013-07-23 Thread Luca Ferrari
On Mon, Jul 22, 2013 at 10:09 AM, Ondrej Chaloupka wrote: > Hello, > > I would like kindly ask for an advice whether and how the transaction log > behavior could be configured. > > I would like to have possibility to differentiate transaction logs for two > databases or schema. I would need such c

Re: [GENERAL] Why are stored procedures looked on so negatively?

2013-07-24 Thread Luca Ferrari
On Wed, Jul 24, 2013 at 2:29 AM, Some Developer wrote: > I've done quite a bit of reading on stored procedures recently and the > consensus seems to be that you shouldn't use them unless you really must. I believe because most developers are not DBAs, and therefore are scared about something they

Re: [GENERAL] Why are stored procedures looked on so negatively?

2013-07-24 Thread Luca Ferrari
On Thu, Jul 25, 2013 at 2:57 AM, Some Developer wrote: > The added advantage of removing load from the app servers so they can > actually deal with serving the app is a bonus. Uhm...I don't know what application you are developing, but I don't buy your explaination. While it is true that you are

Re: [GENERAL] Rule Question

2013-07-25 Thread Luca Ferrari
On Thu, Jul 25, 2013 at 8:44 AM, Andrew Bartley wrote: > create rule cats_test as on update to cats do set a = new.b; > I would use a column trigger attached to the 'a' column. Rules are better for query rewriting rather than from semantic changes. That's my opinion. Luca -- Sent via pgsql-g

Re: [GENERAL] Tablespace on Postgrsql

2013-07-25 Thread Luca Ferrari
On Thu, Jul 25, 2013 at 2:53 AM, devonline wrote: > Our current database size is > > 1 Terabyte > The idea behind tablespaces is to gain I/O scattering data (and therefore requests) across different devices. Therefore you have to identify first if your database can be "scattered" across different

Re: [GENERAL] Rule Question

2013-07-25 Thread Luca Ferrari
The original post was related to the update of b, so I guess it is better to limit the trigger scope to update on such column: CREATE OR REPLACE FUNCTION b_mirror() RETURNS TRIGGER AS $mirror$ BEGIN NEW.a = NEW.b; RETURN NEW; END; $mirror$ LANGUAGE plpgsql; CREATE TRIGGER tr_b_mirror AFTER

Re: [GENERAL] Rule Question

2013-07-25 Thread Luca Ferrari
On Thu, Jul 25, 2013 at 3:02 PM, Tom Lane wrote: > Luca Ferrari writes: >> The original post was related to the update of b, so I guess it is >> better to limit the trigger scope to update on such column: > >> CREATE OR REPLACE FUNCTION b_mirror() RETURNS TRIGGER A

Re: [GENERAL] to know what columns are getting updated

2013-07-29 Thread Luca Ferrari
On Tue, Jul 30, 2013 at 6:05 AM, Sajeev Mayandi wrote: > Hi, > > I have bunch of rules created for tables to implement upsert functionality. > My problem is our tables gets updated from multiple places , non > –necessarily with the same no of columns. I want to figure out columns are > being ask

Re: [GENERAL] incremental dumps

2013-08-01 Thread Luca Ferrari
On Thu, Aug 1, 2013 at 10:59 AM, wrote: > However, the diff files seem to be considerably larger than one would expect. > One obvious part of the problem is the fact that diff shows old and new text, > so e.g. changing the amount of stock for a product with a 1kB description > would generate at

Re: [GENERAL] incremental dumps

2013-08-05 Thread Luca Ferrari
On Fri, Aug 2, 2013 at 6:55 PM, wrote: > thanks for the hint - this is probably one of the things to do. > I have something else in mind, but at present I just suspect that this might > happen: > when I modify data and select _without an ordering_, I am pretty sure to get > the data > in a dif

Re: [GENERAL] Staging Database

2013-08-07 Thread Luca Ferrari
On Wed, Aug 7, 2013 at 6:00 AM, BladeOfLight16 wrote: > The company I work for has a client who has expressed interest in having a > staging database for their data. Staging as in they add data, do some QCing, > then push to a database with an identical schema to make it public. > Fortunately, we'

Re: [GENERAL] Recovery.conf and PITR

2013-08-09 Thread Luca Ferrari
On Fri, Aug 9, 2013 at 10:09 AM, ascot.m...@gmail.com wrote: > Hi, > > I am trying PITR in a test machine (same PG version 9.2.4 same O/S Ubuntu > 12.04 64 bit). All archived WAL files are shipped and saved in > /var/pgsql/data/archive, the latest transaction txid of them is 75666. I > want t

Re: [GENERAL] Recovery.conf and PITR

2013-08-09 Thread Luca Ferrari
On Fri, Aug 9, 2013 at 12:40 PM, wd wrote: > Try add these settings, > > pause_at_recovery_target=true Be warned that this would require a manual completion of the recovery and requires hot_standby that is not specified in the original post. > recovery_target_inclusive=false > Uhm...I guess the

Re: [GENERAL] Recovery.conf and PITR

2013-08-10 Thread Luca Ferrari
On Fri, Aug 9, 2013 at 2:17 PM, ascot.m...@gmail.com wrote: > Is there a way to query the master (it is up and running) about the actual > commit sequence by transaction IDs? The information is within the clogs, but nothing comes into my mind as to inspect from an admin point of view the clog

Re: [GENERAL] Denormalized field

2013-08-19 Thread Luca Ferrari
On Sun, Aug 18, 2013 at 5:56 AM, Robert James wrote: > What's the best way to do this automatically? Can this be done with > triggers? (On UPDATE or INSERT, SET slow_function_f = > slow_function(new_f) ) How? > Define a before trigger that updates your column. For instance: CREATE OR REPLACE FU

Re: [GENERAL] How to know detailed information about HOT(Heap-Only Tuples)?

2013-08-21 Thread Luca Ferrari
On Thu, Aug 22, 2013 at 4:20 AM, 高健 wrote: > according to a table, How many tuples are heap only tuples , and how many > are not? > I believe there are not "hot tuples", a tuple is updated using HOT depending on the indexes defined and the type of update itself. Anyway, you can get an informati

Re: [GENERAL] PostgreSQL 9.2 Logging

2013-08-21 Thread Luca Ferrari
On Wed, Aug 21, 2013 at 9:55 PM, carlosinfl . wrote: > #debug_print_parse = off > #debug_print_rewritten = off > #debug_print_plan = off > #debug_pretty_print = on > #log_checkpoints = off > #log_connections = off > #log_disconnections = off > The debug_* will log, well, debug information (e.g.,

Re: [GENERAL] What is the relationship between checkpoint and wal

2013-08-26 Thread Luca Ferrari
On Mon, Aug 26, 2013 at 4:57 AM, 高健 wrote: > But why "writes the entire content of each disk page to WAL "? > The documentation states that: "The row-level change data normally stored in WAL will not be enough to completely restore such a page during post-crash recovery.". I guess that a mixed pa

Re: [GENERAL] Problem creating index

2013-08-26 Thread Luca Ferrari
On Mon, Aug 26, 2013 at 4:27 PM, Torello Querci wrote: > ERROR: unexpected end of tape Really strange, if I get it right something went wrong while sorting tuples. Is it possible to test with an incremented work_mem value? Luca -- Sent via pgsql-general mailing list (pgsql-general@postgresql

Re: [GENERAL] Problem creating index

2013-08-28 Thread Luca Ferrari
On Wed, Aug 28, 2013 at 1:08 PM, Torello Querci wrote: > Again, is very strange that this data is in the database moreover this > data came from a import procedure and this data is not present in the source > import file. > Really, I think that I get some kind of data corruption I'm sure yo

Re: [GENERAL] pg_dump question (exclude schemas)

2013-08-28 Thread Luca Ferrari
On Wed, Aug 28, 2013 at 9:30 PM, Jay Vee wrote: > $pg_dump --exclude-schema='sch_*' > > this does not seem to exclude all schemas with this pattern ( 'sch_*' ), > anything wrong here? The option is fine and works for me on 9.2.4, I suspect the could do a clash with the -N (exclude-schema).

[GENERAL] xmin increasing within a transaction block?

2017-11-06 Thread Luca Ferrari
Hi all, I suspect this has a trivial explaination, but this is what I'm experiencing: > CREATE TABLE foo( i int ); > BEGIN; * > INSERT INTO foo(i) VALUES( 1 ); * > INSERT INTO foo(i) VALUES( 2 ); * > SELECT xmin, cmin, xmax, cmax, i FROM foo; xmin | cmin | xmax | cmax | i --+--+--+---

Re: [GENERAL] xmin increasing within a transaction block?

2017-11-06 Thread Luca Ferrari
On Mon, Nov 6, 2017 at 1:53 PM, Alvaro Herrera wrote: > Something is using subtransactions there. My first guess would be that > there are triggers with EXCEPTION blocks, but your example doesn't show > any. Or maybe you have event triggers. Thanks, but I don't see any event trigger: > \dy

Re: [GENERAL] xmin increasing within a transaction block?

2017-11-06 Thread Luca Ferrari
On Mon, Nov 6, 2017 at 2:29 PM, Andres Freund wrote: > That doesn't look like plain postgres behaviour to me. Any chance you're > using a pooler in statement mode in front of postgres? None I'm aware of, since the machine is using postgresql locally and I'm connecting to it using the port 5432.

Re: [GENERAL] xmin increasing within a transaction block?

2017-11-06 Thread Luca Ferrari
On Mon, Nov 6, 2017 at 3:26 PM, Tom Lane wrote: > Alvaro Herrera writes: >> Luca Ferrari wrote: >>> Why is xmin greater than the current transaction id (and most notably >>> not "fixed")? > >> Something is using subtransactions there. My first

[GENERAL] process pool

2008-02-25 Thread Luca Ferrari
Hi, sorry for this question, but as far as I know postgresql does not use a process pool, rather a new process is created for any connection on demand. If this is true, what is the reason for this? Thanks, Luca ---(end of broadcast)--- TIP 3: Hav

[GENERAL] split pg_dumpall backups per database

2008-03-11 Thread Luca Ferrari
Hi all, is it possible to instrument pg_dumpall to produce separate sql files for each database it is going to backup? I'd like to keep separate backups of my databases, but using pg_dump can lead to forgetting a database. Thanks, Luca -- Sent via pgsql-general mailing list (pgsql-general@

[GENERAL] regexp_replace in two times?

2008-05-08 Thread Luca Ferrari
Hi all, I used the regexp_replace function to make a substitution over a table, but I got a strange behaviour (please consider I'm not an expert of regex). The idea is to remove the final part of a code, that could be TIF, ISTTIF, tif, isttif, and at the same time consider only the records depen

Re: [GENERAL] regexp_replace in two times?

2008-05-09 Thread Luca Ferrari
On Thursday 8 May 2008 Tom Lane's cat, walking on the keyboard, wrote: > Maybe the original strings had more than one instance of 'TIF'? Opsyou're right, I've checked with a backup copy and I found four records with the double tif pattern. I should have get it beforesorry! Luca -- Sent

[GENERAL] pg_dump & table space

2009-05-31 Thread Luca Ferrari
Hi, is there a way to dump an entire database which has a specific table space without having in the sql file any reference to the tablespace? This can be useful when moving the database from one machine to another (that does not use the tablespace). Any way to achieve that with pg_dump? Thanks

[GENERAL] different execution times of the same query

2009-10-20 Thread Luca Ferrari
Hi all, I'm testing a proprietary driver that connects my old applications to a postgresql database. The problem is that I've got very strange issues about execution times. For instance, the following query: cogedb=> explain analyze SELECT * FROM GMMOVART WHERE DATA >= '01/01/2006' AND DATA

Re: [GENERAL] different execution times of the same query

2009-10-20 Thread Luca Ferrari
On Tuesday 20 October 2009 10:44:13 am Scott Marlowe's cat walking on the keyboard wrote: > Two things. 1: Actually running the query and receiving the results > isn't the same as just running it and throwing them away (what explain > analyze does) and 2: The query may be getting cached in psql i

Re: [GENERAL] How to compare the results of two queries?

2013-09-17 Thread Luca Ferrari
On Tue, Sep 17, 2013 at 4:59 PM, Juan Daniel Santana Rodés wrote: > For example the execution of the function would be something like ... > > select compare('select * from table1', 'select * from table2'); > > For this case the result is false, then the queries are executed on > different tables.

Re: [GENERAL] Partitioning V schema

2013-09-21 Thread Luca Ferrari
On Fri, Sep 20, 2013 at 4:38 AM, Julian wrote: > However, I tend to go with partitions when required to be generated on > demand dynamically and automatically (which probably isn't the case > here). SCHEMAs have other uses, provide a level of security (GRANT) and > useful in design when partitioni

Re: [GENERAL] postgreSQL query via JDBC in different OS taking different running time?

2013-10-08 Thread Luca Ferrari
On Tue, Oct 8, 2013 at 3:48 AM, Aftab Ahmed Chandio wrote: > What do u suggest me, where I need to make performance tuning? w hich > configuration setting must need to modify in the linux? Well, others have already pointed out that you should first measure your query on the server. I would point

Re: [GENERAL] pg_dumpall from a script

2013-10-21 Thread Luca Ferrari
On Tue, Oct 22, 2013 at 7:20 AM, James Sewell wrote: > I need a way to backup either from SQL in PSQL (possibly \!) or from a > PG/PLSQL function to a file with a name set from a :variable. Could it be something like this: # \setenv myFile 'filename' # \! pg_dump > $myFile Unfortunately there

Re: [GENERAL] how to get the connected session pointer ( Archive * AH)

2013-10-22 Thread Luca Ferrari
On Tue, Oct 22, 2013 at 1:32 AM, AI Rumman wrote: > Hi, > > Is there a way in Postgresql C function to get the connected session pointer > ( Archive * AH) > and use it for further execution? If I read pg_archiver.c correctly, the AH pointer is used only during the archiving and is not "globally"

Re: [GENERAL] PGSQL: listing db/role and user/role relationships

2013-11-17 Thread Luca Ferrari
On Sat, Nov 16, 2013 at 1:19 AM, Felipe Gasper wrote: > Hi all, > > How can I retrieve: > > > 1) each role’s privileges on a given DB > Do you mean pg_database.datacl? http://www.postgresql.org/docs/current/static/catalog-pg-database.html > 2) which users have access to a given role > 3)

Re: [GENERAL] PGSQL: listing db/role and user/role relationships

2013-11-17 Thread Luca Ferrari
On Mon, Nov 18, 2013 at 4:00 AM, Felipe Gasper wrote: > One more question: how “stable” are these interfaces? Are they by chance > available via information_schema? Enough stable that they have not changed so much since 8.3. But I'm not used to the information_schema, so I don't know which are e

Re: [GENERAL] wal archive peak during pg_dump

2014-01-10 Thread Luca Ferrari
On Thu, Jan 9, 2014 at 11:42 AM, Willy-Bas Loos wrote: > It doesn't seem logical to me that pg_dump should generate wal, but i > haven't been able to find a different explanation so far. > So to make sure, i want to ask you people: can it be that running pg_dump > creates a lot of wal? Interesti

Re: [GENERAL] manual and autovacuum

2014-02-01 Thread Luca Ferrari
On Sat, Feb 1, 2014 at 9:20 AM, prashant Pandey wrote: > I am looking for actual value. And please if you mention the database > condition along with the recommendation ,it will be helpfull. Maybe the question has to be reversed: what is the condition you are aiming at optimizing? Luca -- Sen

Re: [GENERAL] manual and autovacuum

2014-02-01 Thread Luca Ferrari
On Sat, Feb 1, 2014 at 2:16 PM, prashant Pandey wrote: > Thankyou for your reply. I just want to know that what are the strting > recommended value for the vacuum parameters. The defaults values. Or at least, I will trust such values as good starting values. > As in what values are > unacceptabl

Re: [GENERAL] In a moment of madness I destroyed...

2014-02-03 Thread Luca Ferrari
On Mon, Feb 3, 2014 at 1:33 AM, Jerry Levan wrote: > Would a text pg_dumpall from the mac be portable to the 9.3.x version on the > PAE kerneled > fedora box? > Of course. > I guess I could then possibly use the dblink stuff to suck the tables over > one at a time... Well, if data is still t

Re: [GENERAL] Will modifications to unlogged tables also be flused to disk?

2014-02-15 Thread Luca Ferrari
On Sat, Feb 15, 2014 at 12:38 PM, Clemens Eisserer wrote: > Ok thats really bad news :/ > After reading the discussion about calling unlogged tables "in memory" > or "cached" I actually had high hopes pgql would take advantage of the > fact that data of unlogged tables are not preserved at recover

Re: [GENERAL] another trigger problem

2014-03-08 Thread Luca Ferrari
On Sat, Mar 8, 2014 at 12:36 AM, Susan Cassidy wrote: > You would think that postgres could have output a more helpful error > message, though. I believe that is the correct message: you were concatenating a null string to something, and so nullifying the string you were using for execute. In ot

[GENERAL] help understanding the bitmap heap scan costs

2012-05-21 Thread Luca Ferrari
Hi all, I don't fully understand how is the cost of a bitmap heap scan computed. For instance when the explain output node is similar to the following: Bitmap Heap Scan on test (cost=17376.49..48595.93 rows=566707 width=6) Recheck Cond: ((text1 = 'A'::text) OR (text1 = 'C'::text)) Filter:

[GENERAL] explain doubt

2012-06-25 Thread Luca Ferrari
Hi all, imagine the following simple situation: # CREATE TABLE test( pk serial not null, description text, primary key(pk)); # INSERT INTO test(pk) VALUES(generate_series(1,100 ) ); # VACUUM FULL ANALYZE test; # EXPLAIN SELECT * FROM test WHERE pk = 1 OR pk = 100;

Re: [GENERAL] explain doubt

2012-06-25 Thread Luca Ferrari
On Mon, Jun 25, 2012 at 4:09 PM, Tom Lane wrote: > The short answer though is that this is probably coming from CPU cost > components not disk-access components. Yes of course they are cpu costs, but I'm not able to understand which ones. Is there a way to make PostgreSQL to log the values of the

[GENERAL] pg_dump cannot connect when executing by a script

2010-05-25 Thread Luca Ferrari
Hi all, I've found in the net a lot of problems similar to mine, but not the solution for my case: when I executed pg_dump against a database from a script (that will be executed by cron) I got the following error: pg_dump: [archiver (db)] connection to database "webcalendardb" failed: could no

Re: [GENERAL] pg_dump cannot connect when executing by a script

2010-05-25 Thread Luca Ferrari
On Tuesday 25 May 2010 01:13:40 pm A. Kretschmer's cat walking on the keyboard wrote: > Your unix-scket isn't in /tmp. > > Start psql -h localhost and type: > > show unix_socket_directory; > > This will show you the corrent path to the unix-socket. You can use that > for pg_dump with option -h

[GENERAL] what is the meaning of Datum?

2010-06-23 Thread Luca Ferrari
Hi all, ok this is a silly question, but I've got a doubt: what is the exact meaning of Datum? I see having a look at the macroes (e.g., PG_RETURN_XXX) that a Datum can be used as a pointer or as a single data, that is it can be a reference or a value. Is this right? So for instance the fact tha

[GENERAL] TupleDesc and HeapTuple

2010-07-06 Thread Luca Ferrari
Hi, I don't see any direct link between the TupleDesc structure and the HeapTuple one, and it seems strange to me since to manipulate a tuple you often need the descriptor. What is the trick here? Thanks, Luca -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make change

Re: [GENERAL] TupleDesc and HeapTuple

2010-07-07 Thread Luca Ferrari
On Tuesday, July 06, 2010 09:58:45 pm Alvaro Herrera's cat walking on the keyboard wrote: > You're supposed to know which relation you got the HeapTuple from, so > you get the TupleDesc from there. True, but if I want to pass the heaptuple around I must pass also its tuple desc, or the receiver

[GENERAL] OR or IN ?

2008-10-13 Thread Luca Ferrari
Hi all, I've got a query with a long (>50) list of ORs, like the following: SELECT colB, colC FROM table WHERE colA=X OR colA=Y OR colA=Z OR Is there any difference in how postgresql manages the above query and the following one? SELECT colB, colC FROM table WHERE colA IN (X,Y,Z,...) Whic

Re: [GENERAL] OR or IN ?

2008-10-17 Thread Luca Ferrari
On Friday 17 October 2008 Scott Ribe's cat, walking on the keyboard, wrote: > Older versions of PG were inefficient with larger numbers of elements in an > IN query, and in fact would error out with something about lack of stack > space if you used very many (a few hundred IIRC). > > 8.x something

[GENERAL] pg_dump is ignoring my pgpass file

2008-10-20 Thread Luca Ferrari
Hi all, I'm running 8.2.9, and I've got a curious problem on a database of my cluster. I've got my pgpass file: [EMAIL PROTECTED]:~$ cat ~/.pgpass 192.168.1.2:5432:raydb:ray:xxx 192.168.1.2:5432:hrpmdb:hrpm:x 192.168.1.2:5432:vatcontrollerdb:vatcontroller:xx and if I connect from the

Re: [GENERAL] pg_dump is ignoring my pgpass file

2008-10-22 Thread Luca Ferrari
On Tuesday 21 October 2008 Tom Lane's cat, walking on the keyboard, wrote: > AFAICT the matching of .pgpass entries to a connection attempt is > strictly textual. "sedeldap" != "192.168.1.2" therefore none of > these entries apply. Thanks, I'm able to make entries work only with the ip address

[GENERAL] empty table explain...

2008-10-27 Thread Luca Ferrari
Hi all, I'm curious to know why, if a table is empty, it seems that an ANALYZE of the table does not insert any stats in the pg_stats table, since maybe this could be useful to solve joins including this table. Second, if I execute an EXPLAIN on an empty table, even after an ANALYZE of the table

[GENERAL] drop table but file still exists

2009-05-15 Thread Luca Ferrari
Hi, I'm just curious to know why after a drop table the disk file is emptied but still existent. What is the reason why the file is not deleted immediately? Thanks, Luca -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postg

[GENERAL] problem accessing a database

2006-11-03 Thread Luca Ferrari
Hi all, I've got a database on a server that can be accessed thru the user wwwrun with no password. Now I'd like to copy it on my laptop, so I created the wwwrun (with no password) and thru pg_dump and pg_restore I copied the database on the laptop. The problem is that on the latter I'm unable t

Re: [GENERAL] problem accessing a database

2006-11-03 Thread Luca Ferrari
On Friday 03 November 2006 15:44 A. Kretschmer's cat, walking on the keyboard, wrote: > The settings in your pg_hba.conf. > This file contains comments, read this to understand the proper content. > I guess, you want change the method to trust. But I've already configured pg_hba.conf (maybe in a

  1   2   >