[HACKERS] bad examples in pg_dump README

[Josh Kupershmidt]

The ./src/bin/pg_dump README contains several inoperable examples.
First, this suggestion:

|   or to list tables:
|
|   pg_restore  --table | less

seems bogus, i.e. the --table option requires an argument specifing
which table to restore, and does not "list tables". Next, this
suggested command:

|  pg_restore  -l --oid --rearrange | less

hasn't worked since 7.4 or thereabouts, since we don't have
--rearrange or --oid anymore. (I'm not sure we ever had --oid, maybe
it was supposed to be --oid-order?). Then, three examples claim we
support a "--use" flag, e.g.

|  pg_restore backup.bck --use=toc.lis > script.sql

where presumably "--use-list" was meant. Further little gripes with
this README include mixed use of tabs and spaces for the examples, and
lines running over the 80-column limit which at least some of our
other READMEs seem to honor.

I started out attempting to fix up the README, keeping the original
example commands intact, but upon further reflection I think the
examples of dumping, restoring, and selective-restoring in that REAMDE
don't belong there anyway. There are already better examples of such
usage in the pg_dump/pg_restore documentation pages, and IMO that's
where such generally-useful usage information belongs.

I propose slimming down the pg_dump README, keeping intact the
introductory notes and details of the tar format.

Josh


pg_dump_README.diff
Description: Binary data

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] PGCon 2013 - CFP & unconference day

[Dan Langille]

Folks,

The PGCon Call for Papers went out last month.  But you have about two weeks
left to respond.  If you are doing something interesting with PostgreSQL, 
please submit
a proposal.  You might be one of the backend hackers or work on a
PostgreSQL related project and want to share your know-how with
others. You might be developing an interesting system using PostgreSQL
as the foundation. Perhaps you migrated from another database to
PostgreSQL and would like to share details.  These, and other stories
are welcome. Both users and developers are encouraged to share their
experiences.

See this URL for details:

   http://lists.pgcon.org/pipermail/pgcon-announce/2012-December/90.html 

New this year, we are having an unconference on the Saturday right after the
conference. The content of the unconference will be determined, on the day,
by the attendees. We expect heavy attendance by developers and users of 
PostgreSQL.

Be sure to submit your proposal soon because time is running out.

-- 
Dan Langille - http://langille.org



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] pg_upgrade test script creates port conflicts in parallel testing

[Tom Lane]

Andrew Dunstan  writes:
> On 01/03/2013 12:58 PM, Tom Lane wrote:
>> Does anyone have an objection to fixing the pg_upgrade test script
>> to suppress the TCP socket?

> Should be OK. We can't do that on Windows, though, so please make it 
> conditional so we don't break Mingw buildfarm members. The test script 
> already contains a few Windows variants.

I'm planning to do it like this:

 testhost=`uname -s`
 
+case $testhost in
+   MINGW*) LISTEN_ADDRESSES="localhost" ;;
+   *)  LISTEN_ADDRESSES="" ;;
+esac
+
+POSTMASTER_OPTS="-F -c listen_addresses=$LISTEN_ADDRESSES"
+
 temp_root=$PWD/tmp_check

which matches the existing Windows-specific switches.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Re: Proposal: Store "timestamptz" of database creation on "pg_database"

[Joshua D. Drake]

On 01/03/2013 02:30 PM, Kevin Grittner wrote:


Andrew Dunstan wrote:


I don't especially have a horse in the race, but ISTM that if you want
the information you want it to be able to persist across dump/restore,
at least optionally. If you can happily lose it when you're forced to
recover using a logical dump then it's not that important to you.


On that point I guess we will just disagree. In my experience, if
you are OK with a periodic pg_dump for your primary backup
technique, then the data is just not that important to you.


Or the data doesn't change that much but in principle I agree with you.

JD


--
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC
@cmdpromptinc - 509-416-6579


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Re: Proposal: Store "timestamptz" of database creation on "pg_database"

[Kevin Grittner]

Andrew Dunstan wrote:

> I don't especially have a horse in the race, but ISTM that if you want 
> the information you want it to be able to persist across dump/restore, 
> at least optionally. If you can happily lose it when you're forced to 
> recover using a logical dump then it's not that important to you.

On that point I guess we will just disagree. In my experience, if
you are OK with a periodic pg_dump for your primary backup
technique, then the data is just not that important to you. And if
you drop and re-create a table from pg_dump output, that event is
worth noting -- I would rather see the timestamp of applying the
pg_dump output.

When it comes to forensics, why don't we feel that it is worth
preserving next available xid and every tuple's xmin and xmax
through pg_dump? I don't think we should, but the arguments against
trying to do it seem similar to me. They are newly created tables
when you run the SQL generated by pg_dump, with fresh rows and
indexes. To pretend otherwise seems to me to reduce the value of
the feature.

On the other hand, having one central way to deal with it for all
object types seems to increase the value of the feature.

-Kevin


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Re: Proposal: Store "timestamptz" of database creation on "pg_database"

[Andrew Dunstan]

On 01/03/2013 04:51 PM, Kevin Grittner wrote:

Robert Haas wrote:

Christopher Browne  wrote:

these timestamps Should Not be captured or carried forward by
pg_dump.
If we put a creation time into pg_database or pg_class, then
streaming replication will, as a "physical" replication
mechanism, carry the timestamp forward into replicas
And in contrast, I'd expect Andres Freund's logical replication
infrastructure *NOT* to carry these dates over, but rather to
establish fresh new creation dates on a replica. (And from a
forensic perspective, that's a perfectly fine thing.)

I agree all around.

+1

My analogy would be to xmin in tuples. Anything that preserves that
should preserve table creation timestamp. If the tuples' xmin
values in the table receiving the data differ, the creation
timestamp should, too.

In my experience, this would have been valuable forensic
information many times. Preserving xmin rather than aggressively
freezing never has been or would have been useful to me.




I don't especially have a horse in the race, but ISTM that if you want 
the information you want it to be able to persist across dump/restore, 
at least optionally. If you can happily lose it when you're forced to 
recover using a logical dump then it's not that important to you.



cheers

andrew


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Re: Proposal: Store "timestamptz" of database creation on "pg_database"

[Kevin Grittner]

Robert Haas wrote:
> Christopher Browne  wrote:

>> these timestamps Should Not be captured or carried forward by
>> pg_dump.

>> If we put a creation time into pg_database or pg_class, then
>> streaming replication will, as a "physical" replication
>> mechanism, carry the timestamp forward into replicas

>> And in contrast, I'd expect Andres Freund's logical replication
>> infrastructure *NOT* to carry these dates over, but rather to
>> establish fresh new creation dates on a replica. (And from a
>> forensic perspective, that's a perfectly fine thing.)
> 
> I agree all around.

+1

My analogy would be to xmin in tuples. Anything that preserves that
should preserve table creation timestamp. If the tuples' xmin
values in the table receiving the data differ, the creation
timestamp should, too.

In my experience, this would have been valuable forensic
information many times. Preserving xmin rather than aggressively
freezing never has been or would have been useful to me.

-Kevin


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Reminder: CFP for pgCon closes January 19th

[Josh Berkus]

On 1/3/13 1:02 PM, Josh Berkus wrote:
> Hackers,
> 
> As a reminder, we are going to close the call for presentations for
> pgCon on January 15th this year, in an effort to get speakers notified
> sooner.  Please submit your talks as soon as you can.

Correction, the 19th.

Also, a URL would help: http://www.pgcon.org/2013/papers.php


-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Reminder: CFP for pgCon closes January 15th

[Josh Berkus]

Hackers,

As a reminder, we are going to close the call for presentations for
pgCon on January 15th this year, in an effort to get speakers notified
sooner.  Please submit your talks as soon as you can.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Re: Proposal: Store "timestamptz" of database creation on "pg_database"

[Robert Haas]

On Thu, Jan 3, 2013 at 12:54 PM, Christopher Browne  wrote:
> Yep, and I think that the behaviour of tar pretty nicely characterizes
> what's troublesome here.  It is quite likely that a tar run will *capture*
> the creation time of a file, but if you pull data from a tar archive, it is
> by no means obvious that the filesystem can or will accept that date
> and apply it to the extracted copy.
>
> I'd contrast pg_dump with tar in that the former is intended as more of
> a "logical" dump than the latter, so that, in keeping with Greg Stark's
> comments, these timestamps Should Not be captured or carried forward
> by pg_dump.
>
> The interaction with streaming replication is pretty analogous to the
> interaction one might expect to get out of filesystem snapshot
> technologies like DRBD, zfs, btrfs, LVM.  If we put a creation time
> into pg_database or pg_class, then streaming replication will, as a
> "physical" replication mechanism, carry the timestamp forward into
> replicas, in pretty much exactly the same fashion that timestamps
> would be carried onto clones/snapshots by the filesystem
> snapshotting systems.
>
> And in contrast, I'd expect Andres Freund's logical replication
> infrastructure *NOT* to carry these dates over, but rather to establish
> fresh new creation dates on a replica.  (And from a forensic perspective,
> that's a perfectly fine thing.)

I agree all around.

And to take a step back and speak a bit more broadly about this, I
believe that, more and more, we can't rely on the operating system to
do things for us any more.  Five or ten years ago, maybe people were
running Linux, and PostgreSQL was a part of that.  Now, more and more,
people are running PostgreSQL, and Linux (or Windows, or some other
OS) is a way to make that happen.  At least when I talk to customers,
places where the OS behavior bleeds into what the database server does
are not viewed as features.  Telling people that we use the OS
collation facilities, or that we use the OS buffer cache, or that we
don't provide a scheduler because Linux has cron and Windows has
scheduled tasks, or that people should examine file timestamps to try
to work out when a relation was created results in bemusement, or
sometimes incredulity.  Many people are understanding of the idea that
we don't have the manpower to implement everything ourselves, but very
few customers I've spoken with think that planning to rely on the OS
facilities is a sound design principle.  It's true, as we've often
said here, that leveraging the OS facilities means that we get the
benefit of improving OS facilities "for free" - but it also means that
we never exceed what the OS facilities are able to provide.  And
frankly, as in this case, the OS facilities are often poorly suited to
what users actually want.  We obviously do not want to go bonkers and
take over everything from the OS, but I don't think we should be
afraid to rotate the knob a little bit in that direction.  The fact
that people are pushing us to go there is a sign of our success.  We
are the ecosystem.

I do have a concern about catalog bloat.  I think it would be easy to
add so many knobs that we end up slowing the system down and bloating
the size of an otherwise-empty database, or one with lots of SQL
objects.  Let's not do that.  But let's not do nothing, either.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Review of Row Level Security

[Stephen Frost]

KaiGai,

* Kohei KaiGai (kai...@kaigai.gr.jp) wrote:
> IMO, only "parser" should accept command types except for ALL but
> raise an error something like "it is not supported yet" to protect from
> syntax conflicts.

Right, I agree with this.

> Right now, I plan to submit a revised patch with the syntax support
> above, and without support for INSERT or NEW of UPDATE checks,
> as minimum set of functionality for v9.3.

Sounds good.

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] Feature Request: pg_replication_master()

[Simon Riggs]

On 3 January 2013 18:35, Josh Berkus  wrote:
> Robert,
>
>> In my view, the biggest problem with recovery.conf is that the
>> parameters in there are not GUCs, which means that all of the
>> infrastructure that we've built for managing GUCs does not work with
>> them.  As an example, when we converted recovery.conf to use the same
>> lexer that the GUC machinery uses, it allowed recovery.conf values to
>> be specified unquoted in the same circumstances where that was already
>> possible for postgresql.conf.  But, you still can't use SHOW or
>> pg_settings with recovery.conf parameters, and I think pg_ctl reload
>> doesn't work either.  If we make these parameters into GUCs, then
>> they'll work the same way everything else works.  Even if (as seems
>> likely) we end up still needing a trigger file (or a special pg_ctl
>> mode) to initiate recovery, I think that's probably a win.
>
> I agree that it would be an improvement, and I would be happy just to
> see the parameters become GUCs.

That may be possible in 9.3 since we have a patch from Fujii-san. I'll
hack that down to just the GUC part once we start the next CF.

My personal priority is the shutdown checkpoint patch over that though.

> I'm just saying that I'll still be pushing to get rid of the requirement
> for recovery.conf in 9.4, that's all.

No pushing required. When we have a reasonable proposal that improves
on the current state, we can implement that.

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

[Magnus Hagander]

On Thu, Jan 3, 2013 at 8:31 PM, Tomas Vondra  wrote:
> On 3.1.2013 18:47, Heikki Linnakangas wrote:
>> On 03.01.2013 01:15, Tomas Vondra wrote:
>>> 2) a new global/stat directory
>>> --
>>>
>>> The pgstat.stat file was originally saved into the "global" directory,
>>> but with so many files that would get rather messy so I've created a new
>>> global/stat directory and all the files are stored there.
>>>
>>> This also means we can do a simple "delete files in the dir" when
>>> pgstat_reset_all is called.
>>
>> How about creating the new directory as a direct subdir of $PGDATA,
>> rather than buried in global? "global" is supposed to contain data
>> related to shared catalog relations (plus pg_control), so it doesn't
>> seem like the right location for per-database stat files. Also, if we're
>> going to have admins manually zapping the directory (hopefully when the
>> system is offline), that's less scary if the directory is not buried as
>> deep.
>
> That's clearly possible and it's a trivial change. I was thinking about
> that actually, but then I placed the directory into "global" because
> that's where the "pgstat.stat" originally was.

Yeah, +1 for a separate directory not in global.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

[Tomas Vondra]

On 3.1.2013 18:47, Heikki Linnakangas wrote:
> On 03.01.2013 01:15, Tomas Vondra wrote:
>> 2) a new global/stat directory
>> --
>>
>> The pgstat.stat file was originally saved into the "global" directory,
>> but with so many files that would get rather messy so I've created a new
>> global/stat directory and all the files are stored there.
>>
>> This also means we can do a simple "delete files in the dir" when
>> pgstat_reset_all is called.
> 
> How about creating the new directory as a direct subdir of $PGDATA,
> rather than buried in global? "global" is supposed to contain data
> related to shared catalog relations (plus pg_control), so it doesn't
> seem like the right location for per-database stat files. Also, if we're
> going to have admins manually zapping the directory (hopefully when the
> system is offline), that's less scary if the directory is not buried as
> deep.

That's clearly possible and it's a trivial change. I was thinking about
that actually, but then I placed the directory into "global" because
that's where the "pgstat.stat" originally was.

Tomas


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] pg_upgrade test script creates port conflicts in parallel testing

[Andrew Dunstan]

On 01/03/2013 12:58 PM, Tom Lane wrote:

I've been getting complaints lately about failures of parallel builds
of the Fedora Postgres RPMs on the same machine.  I just figured out
what's going on: the pg_upgrade regression test script starts test
postmasters using the default value of listen_addresses, which means
that they try to bind to TCP port 50432 on localhost, which means the
test fails if there's more than one concurrent instance.  This does
not happen for the main regression tests, nor for any other contrib
module, because pg_regress.c's postmaster-starting code explicitly
sets listen_addresses to empty, so that only the Unix socket is
active.  (RPM building in Fedora generally happens in a chroot, so
there is no conflict of Unix sockets - they're not in the same /tmp.)

pg_upgrade itself also sets listen_addresses to empty; it's only
the test script that hasn't gotten the memo.

Does anyone have an objection to fixing the pg_upgrade test script
to suppress the TCP socket?




Should be OK. We can't do that on Windows, though, so please make it 
conditional so we don't break Mingw buildfarm members. The test script 
already contains a few Windows variants.


cheers

andrew



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Feature Request: pg_replication_master()

[Josh Berkus]

Robert,

> In my view, the biggest problem with recovery.conf is that the
> parameters in there are not GUCs, which means that all of the
> infrastructure that we've built for managing GUCs does not work with
> them.  As an example, when we converted recovery.conf to use the same
> lexer that the GUC machinery uses, it allowed recovery.conf values to
> be specified unquoted in the same circumstances where that was already
> possible for postgresql.conf.  But, you still can't use SHOW or
> pg_settings with recovery.conf parameters, and I think pg_ctl reload
> doesn't work either.  If we make these parameters into GUCs, then
> they'll work the same way everything else works.  Even if (as seems
> likely) we end up still needing a trigger file (or a special pg_ctl
> mode) to initiate recovery, I think that's probably a win.

I agree that it would be an improvement, and I would be happy just to
see the parameters become GUCs.

I'm just saying that I'll still be pushing to get rid of the requirement
for recovery.conf in 9.4, that's all.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Re: Proposal: Store "timestamptz" of database creation on "pg_database"

[Christopher Browne]

On Thu, Jan 3, 2013 at 12:27 PM, Robert Haas  wrote:
> On Thu, Jan 3, 2013 at 11:15 AM, Hannu Krosing  wrote:
>> This is what I did with my sample pl/python function ;)
>
> Yeah, except that the "c" in "ctime" does not stand for create, and
> therefore the function isn't necessarily reliable.  The problem is
> even worse for tables, where a rewrite may remove the old file and
> create a new one.  I mean, I'm not stupid about this: when I need to
> figure this kind of stuff out, I do in fact look at the file times -
> mtime, ctime, atime, whatever there is.  Sometimes that turns out to
> be helpful, and sometimes it doesn't.  An obvious example of the
> latter is when you're looking at a bunch of files that have just been
> untarred from a backup device.

Yep, and I think that the behaviour of tar pretty nicely characterizes
what's troublesome here.  It is quite likely that a tar run will *capture*
the creation time of a file, but if you pull data from a tar archive, it is
by no means obvious that the filesystem can or will accept that date
and apply it to the extracted copy.

I'd contrast pg_dump with tar in that the former is intended as more of
a "logical" dump than the latter, so that, in keeping with Greg Stark's
comments, these timestamps Should Not be captured or carried forward
by pg_dump.

The interaction with streaming replication is pretty analogous to the
interaction one might expect to get out of filesystem snapshot
technologies like DRBD, zfs, btrfs, LVM.  If we put a creation time
into pg_database or pg_class, then streaming replication will, as a
"physical" replication mechanism, carry the timestamp forward into
replicas, in pretty much exactly the same fashion that timestamps
would be carried onto clones/snapshots by the filesystem
snapshotting systems.

And in contrast, I'd expect Andres Freund's logical replication
infrastructure *NOT* to carry these dates over, but rather to establish
fresh new creation dates on a replica.  (And from a forensic perspective,
that's a perfectly fine thing.)

I imagine that we should be careful to put these forensic timestamps
onto things with some care.

- Putting them on pg_database seems like a fine idea.
- Putting them on pg_attribute seems mighty dodgy; I don't expect I'd
often care, and this change increases the size of an extremely heavily
accessed system table
- I am equivocal about putting them on pg_class.  That increases the
size of a pretty big, heavily accessed system table.
- Perhaps there are other relevant tables (pg_event_trigger,
pg_extension, FDW tables, pg_language, pg_proc, pg_tablespace); I
don't feel so strongly about them, but if you're puzzling over what
went wrong with an extension, event trigger, or FDW, time of creation
seems like it might be useful.
-- 
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] pg_upgrade test script creates port conflicts in parallel testing

[Tom Lane]

I've been getting complaints lately about failures of parallel builds
of the Fedora Postgres RPMs on the same machine.  I just figured out
what's going on: the pg_upgrade regression test script starts test
postmasters using the default value of listen_addresses, which means
that they try to bind to TCP port 50432 on localhost, which means the
test fails if there's more than one concurrent instance.  This does
not happen for the main regression tests, nor for any other contrib
module, because pg_regress.c's postmaster-starting code explicitly
sets listen_addresses to empty, so that only the Unix socket is
active.  (RPM building in Fedora generally happens in a chroot, so
there is no conflict of Unix sockets - they're not in the same /tmp.)

pg_upgrade itself also sets listen_addresses to empty; it's only
the test script that hasn't gotten the memo.

Does anyone have an objection to fixing the pg_upgrade test script
to suppress the TCP socket?

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: PATCH: Split stats file per database WAS: [HACKERS] autovacuum stress-testing our system

[Heikki Linnakangas]

On 03.01.2013 01:15, Tomas Vondra wrote:

2) a new global/stat directory
--

The pgstat.stat file was originally saved into the "global" directory,
but with so many files that would get rather messy so I've created a new
global/stat directory and all the files are stored there.

This also means we can do a simple "delete files in the dir" when
pgstat_reset_all is called.


How about creating the new directory as a direct subdir of $PGDATA, 
rather than buried in global? "global" is supposed to contain data 
related to shared catalog relations (plus pg_control), so it doesn't 
seem like the right location for per-database stat files. Also, if we're 
going to have admins manually zapping the directory (hopefully when the 
system is offline), that's less scary if the directory is not buried as 
deep.


- Heikki


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] pg_retainxlog for inclusion in 9.3?

[Robert Haas]

On Thu, Jan 3, 2013 at 11:32 AM, Magnus Hagander  wrote:
> Any particular reason? It goes pretty tightly together with
> pg_receivexlog, which is why I'd prefer putting it alongside that one.
> But if you have a good argument against it, I can change my mind :)

Mostly that it seems like a hack, and I suspect we may come up with a
better way to do this in the future.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Writing Trigger Functions in C

[Charles Gomes]

> Date: Sat, 29 Dec 2012 23:45:06 -0500
> Subject: Re: [HACKERS] Writing Trigger Functions in C
> From: robertmh...@gmail.com
> To: charle...@outlook.com
> CC: cbbro...@gmail.com; pgsql-hackers@postgresql.org
>
> On Mon, Dec 24, 2012 at 10:43 AM, Charles Gomes  wrote:
> > And I had no performance improvements at all.
> > Took the same time as with the previous EXECUTE statement;
> >
> > I don't see what am I doing wrong.
>
> You might not be doing anything wrong. Triggers ARE slow.
>
> If you have "perf" on your system, you could use "perf top" or "perf
> record -a" to find out where the CPU time is going while you're doing
> stuff that fires this trigger. That might provide some clues about
> how to optimize. But it may be that you'll get a completely flat
> profile, or something that otherwise boils down to ... triggers are
> slow.
>
> In answer to your original question, there is a C language trigger in
> contrib/tcn. But, without some proof that the use of PL/pgsql is the
> problem, I don't know how far down that road it's worth going. It
> might be worth writing a C trigger that does nothing but return the
> original tuple, or even a PL/pgsql one. This obviously wouldn't
> accomplish anything as far as partitioning goes, but it would let you
> measure the overhead of calling a no-op trigger, which could be a
> useful thing to know.
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company

I've translated the trigger to C and performance had not increased, just like 
you guys said. I've created an article with the trigger and the metrics in case 
anyone becomes interested in the future 
http://www.charlesrg.com/linux/71-postgresql-partitioning-the-database-the-fastest-way
  

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Re: Proposal: Store "timestamptz" of database creation on "pg_database"

[Robert Haas]

On Thu, Jan 3, 2013 at 11:15 AM, Hannu Krosing  wrote:
> This is what I did with my sample pl/python function ;)

Yeah, except that the "c" in "ctime" does not stand for create, and
therefore the function isn't necessarily reliable.  The problem is
even worse for tables, where a rewrite may remove the old file and
create a new one.  I mean, I'm not stupid about this: when I need to
figure this kind of stuff out, I do in fact look at the file times -
mtime, ctime, atime, whatever there is.  Sometimes that turns out to
be helpful, and sometimes it doesn't.  An obvious example of the
latter is when you're looking at a bunch of files that have just been
untarred from a backup device.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Print b-tree tuples

[Samuel Vogel]

Hello,

I'm trying to print out the tuples in the b-tree nodes for analysis, but 
when iterate over more than the first entry of the tuples (scanKey++), I 
strangely get the error below on query execution:

ERROR:  relation "simpletest" does not exist
LINE 1: SELECT * FROM simpletest WHERE id = 50;

Any help with reviewing my my small attached patch would be greatly 
appreciated!


Regards,
Samuel Vogel
diff --git src/backend/access/nbtree/nbtsearch.c 
src/backend/access/nbtree/nbtsearch.c
index 
ac98589477b876cfc8470ea03fb39fa43d5ea9c5..e07ae925332a923da73eb2bb28809e79f4bd7a1b
 100644
--- src/backend/access/nbtree/nbtsearch.c
+++ src/backend/access/nbtree/nbtsearch.c
@@ -203,6 +203,37 @@ _bt_moveright(Relation rel,
return buf;
 }
 
+void
+_bt_printindextuple(Relation rel,
+   int keysz,
+   ScanKey scankey,
+   Page page,
+   OffsetNumber offnum)
+{
+   TupleDesc   itupdesc = RelationGetDescr(rel);
+   BTPageOpaqueopaque = (BTPageOpaque) PageGetSpecialPointer(page);
+   IndexTuple  itup = (IndexTuple) PageGetItem(page, 
PageGetItemId(page, offnum));;
+   int i;
+
+   fprintf(stdout, "%d: ", offnum);
+
+   for (i = 1; i <= keysz; i++)
+   {
+   Datum   datum;
+   boolisNull;
+   int32   result;
+
+   datum = index_getattr(itup, scankey->sk_attno, itupdesc, 
&isNull);
+
+   if (i > 1) fprintf(stdout, ", ");
+   fprintf(stdout, "%d", DatumGetInt32(datum));
+
+   scankey++;
+   }
+
+   fprintf(stdout, "\n");
+}
+
 /*
  * _bt_binsrch() -- Do a binary search for a key on a particular page.
  *
@@ -260,6 +291,16 @@ _bt_binsrch(Relation rel,
if (high < low)
return low;
 
+   fprintf(stdout, "---\n");
+   fprintf(stdout, "low: %d, high: %d\n", low, high);
+
+   int j;
+   for (j = low; j <= high; j++) {
+   _bt_printindextuple(rel, keysz, scankey, page, j);
+   }
+
+   fprintf(stdout, "---\n");
+
/*
 * Binary search to find the first key on the page >= scan key, or first
 * key > scankey when nextkey is true.
diff --git src/include/access/nbtree.h src/include/access/nbtree.h
index 
eef67f54b504e73b10d4e1b2f12472eeb8481ffa..25b86020af3bdb0e3d2f1c6e730c70a68fc6a9a1
 100644
--- src/include/access/nbtree.h
+++ src/include/access/nbtree.h
@@ -643,6 +643,8 @@ extern int  _bt_pagedel(Relation rel, Buffer buf, BTStack 
stack);
 /*
  * prototypes for functions in nbtsearch.c
  */
+extern void _bt_printindextuple(Relation rel, int keysz,
+   ScanKey scankey, Page page, OffsetNumber offnum);
 extern BTStack _bt_search(Relation rel,
   int keysz, ScanKey scankey, bool nextkey,
   Buffer *bufP, int access);

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Proposal: Store "timestamptz" of database creation on "pg_database"

[Greg Stark]

On Thu, Jan 3, 2013 at 2:06 AM, Stephen Frost  wrote:
> I'd be alright with this also, tbh.  Not preserving such information
> across pg_dump's wouldn't really be all *that* much of a loss.

I think it would be mandatory for pg_dump not to restore this info
actually. A fair amount of work has gone into pg_dump -s to ensure
that the output is identical for identical databases.  OIDs were
removed and the sort order was changed to be deterministic for
example. Any "alter table set creation time 'xxx'" will defeat that
entirely.

When last I managed a production Postgres database I would use pg_dump
-s to regenerate a schema file that was checked into revision control.
And when I migrated changes live I would rerun pg_dump -s and diff
that against the checked in schema.

-- 
greg


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] pg_retainxlog for inclusion in 9.3?

[Magnus Hagander]

On Thu, Jan 3, 2013 at 3:13 PM, Robert Haas  wrote:
> On Tue, Jan 1, 2013 at 10:10 AM, Magnus Hagander  wrote:
>> So, it turns out the reason I got no feedback on this tool, was that I
>> forgot both to email about and to actually push the code to github :O
>> So this is actually code that's almost half a year old and that I was
>> supposed to submit for the first or second commitfest. Oops.
>>
>> So, the tool and a README for it right now can be found at
>> https://github.com/mhagander/pg_retainxlog for the time being.
>>
>> The idea behind the tool is to plug a hole in the case when
>> pg_receivexlog is used for log archiving, which is that you still need
>> a "blocking" archive_command in order to make sure that files aren't
>> recycled on the master. So for 9.2 you can do this with an
>> archive_command that checks if the file has appeared properly on the
>> slave - but that usually means you're back at requiring ssh
>> connectivity between the machines, for example. Even though this
>> information is actually avialable on the master...
>>
>> This can also be of use to pure replication scenarios, where you don't
>> know how to tune wal_keep_segments, but using actual live feedback
>> instead of guessing.
>>
>> When pg_retainxlog is used as an archive_command, it will check the
>> pg_stat_replication view instead of checking the slave. It will then
>> only return ok once the requested logfile has been replicated to the
>> slave. By default it will look for a replication client named
>> pg_receivexlog, but it supports overriding the query with anything -
>> so you can say things like "needs to have arrived on at least two
>> replication slaves before we consider it archived". Or if used instead
>> of wal_keep_segmnets, needs to have arrived at *all* replication
>> slaves.
>>
>> Is this a tool that people would like to see included in the general
>> toolchain? If so, I'll reformat it to work in the general build
>> environment and submit it for the last commitfest.
>>
>> (comments on the code itself are of course also welcome)
>
> I would tend to vote for putting this in contrib rather than src/bin.
> But apart from that I have no objection to the idea.

Any particular reason? It goes pretty tightly together with
pg_receivexlog, which is why I'd prefer putting it alongside that one.
But if you have a good argument against it, I can change my mind :)

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Proposal: Store "timestamptz" of database creation on "pg_database"

[Alvaro Herrera]

Fabrízio de Royes Mello escribió:

> As Peter said we can start add it for a few commands in one release (maybe
> first for shared objects) and then for a few more commands in a next
> release, and next... until we cover all commands...

No, he was describing a pessimistic scenario that he doesn't want us to
be on.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Re: Proposal: Store "timestamptz" of database creation on "pg_database"

[Hannu Krosing]

On 01/03/2013 03:09 PM, Robert Haas wrote:

On Thu, Jan 3, 2013 at 8:46 AM, Hannu Krosing  wrote:

How is "what does database creation date mean?" a different question ?

It is same question as :

what is the creation date of db when I create a replica of my database from
backup?

does it depend on how I restore my replica ?

can I restore it from pg_dump and still have same creation date ?

etc. etc.

...

Of course, these objections miss the point.  Even an imperfect
solution will be better than no solution at all.  And it is very
likely that if we simply provide whatever hydrating agent lies closest
to hand, we'll get full marks.

This is what I did with my sample pl/python function ;)

Similarly, in the present situation, I believe that there is little
reason to suppose that the simplest possible implementation of this
feature won't resolve the overwhelming majority of the needs that
people have.  We have many features about which users might raise the
same kinds of questions that you are raising about this one, and they
do, and those questions are perfectly valid.  But they are not reasons
to remove those features, and the questions you raise are not reasons
to avoid having this one.  They are simply things that must be
documented and explained, just as we need to do with every other
feature we ship.  And if someone is not perfectly happy with the
design, it won't be the first time for that, either.  It does not mean
that it's worse than not having anything.


If we made sure that things like CLUSTER or moving to
another tablespace would keep file ctime, then this would
answer 98% of requests .

Even without keeping them, this would be giving the chap "water" ...

So my proposal is to just have a pg_database_createtime(dbname)
function and solve the simple part of the problem.

-
Hannu



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] dynamic SQL - possible performance regression in 9.2

[Tom Lane]

Peter Eisentraut  writes:
> On 1/1/13 6:48 PM, Tom Lane wrote:
>> Here's a draft patch for that.

> This didn't make a difference in my test case.  I might have to do some
> bisecting to find where the problem was introduced.

Could we see the test case?  Or at least oprofile results for it?

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Proposal: Store "timestamptz" of database creation on "pg_database"

[Fabrízio de Royes Mello]

On Thu, Jan 3, 2013 at 12:30 PM, Alvaro Herrera 
wrote:
>
> Peter Eisentraut escribió:
>
> > If we're going to store object creation time, I think we should do it
> > for all objects, stored in a separate catalog, like pg_depend or
> > pg_description, keyed off classid, objectid.  And have a simple C
> > function to call to update the information stored there.
>
> +1
>

+1

> We require two catalogs though, one shared, one database-local.
>

Have you a suggestion for the names of this new two catalogs?


> Would we track ctime of subsidiary objects such as constraints etc?
>

If we're going to this way I think yes...

As Peter said we can start add it for a few commands in one release (maybe
first for shared objects) and then for a few more commands in a next
release, and next... until we cover all commands...

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Blog sobre TI: http://fabriziomello.blogspot.com
>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello

Re: [HACKERS] Proposal: Store "timestamptz" of database creation on "pg_database"

[Fabrízio de Royes Mello]

On Thu, Jan 3, 2013 at 11:41 AM, Stephen Frost  wrote:

> * Fabrízio de Royes Mello (fabriziome...@gmail.com) wrote:
> > On Thu, Jan 3, 2013 at 11:13 AM, Stephen Frost 
> wrote:
> > > Please use hard-tabs (not spaces) and the column should come before the
> > > variable length records (see the comment in pg_database.h).
> >
> > You all right... I fixed it in attached patch.
>
> You also need to fix the Anum_* values to match what's in the struct
> definition now..
>
> I'd recommend that you look over the code more closely and ensure that
> you're ordering everything correctly throughout and that it all makes
> sense..
>
>
Now I fixed it.

Regards,

-- 
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Blog sobre TI: http://fabriziomello.blogspot.com
>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello


pg_database_add_datcreated_column_v3.patch
Description: Binary data

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] dynamic SQL - possible performance regression in 9.2

[Peter Eisentraut]

On 1/1/13 6:48 PM, Tom Lane wrote:
> I wrote:
>> > I'm inclined to think that Heikki's patch doesn't go far enough, if we
>> > want to optimize behavior in this case.  What we really want to happen
>> > is that parsing, planning, and execution all happen in the caller's
>> > memory context, with no copying of parse or plan trees at all - and we
>> > could do without overhead such as dependency extraction and invalidation
>> > checking, too.  This would make SPI_execute a lot more comparable to the
>> > behavior of exec_simple_query().
> Here's a draft patch for that.

This didn't make a difference in my test case.  I might have to do some
bisecting to find where the problem was introduced.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Proposal: Store "timestamptz" of database creation on "pg_database"

[Alvaro Herrera]

Peter Eisentraut escribió:

> If we're going to store object creation time, I think we should do it
> for all objects, stored in a separate catalog, like pg_depend or
> pg_description, keyed off classid, objectid.  And have a simple C
> function to call to update the information stored there.

+1

We require two catalogs though, one shared, one database-local.

Would we track ctime of subsidiary objects such as constraints etc?

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Proposal: Store "timestamptz" of database creation on "pg_database"

[Robert Haas]

On Thu, Jan 3, 2013 at 9:18 AM, Pavel Stehule  wrote:
> 2013/1/3 Peter Eisentraut :
>> On 1/2/13 11:08 PM, Fabrízio de Royes Mello wrote:
>>> The attached patch add a new column into 'pg_database' called
>>> 'datcreated' to store the timestamp of database creation.
>>>
>>> If this feature is approved I could extend it to add a column into
>>> 'pg_class' to store creation timestamp too.
>>
>> While I'm entirely in favor of this feature in general, I think this is
>> the wrong way to approach it.  It will end up like the CREATE OR REPLACE
>> support: We add it for a few commands in one release, for a few more
>> commands in the next release, for almost all commands in the following
>> release, and now we're still not done.
>>
>> If we're going to store object creation time, I think we should do it
>> for all objects, stored in a separate catalog, like pg_depend or
>> pg_description, keyed off classid, objectid.  And have a simple C
>> function to call to update the information stored there.
>>
>> That would also make storing the modification time, which I'd ask for
>> next, easier.
>
> +1

+1

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Proposal: Store "timestamptz" of database creation on "pg_database"

[Pavel Stehule]

2013/1/3 Peter Eisentraut :
> On 1/2/13 11:08 PM, Fabrízio de Royes Mello wrote:
>> The attached patch add a new column into 'pg_database' called
>> 'datcreated' to store the timestamp of database creation.
>>
>> If this feature is approved I could extend it to add a column into
>> 'pg_class' to store creation timestamp too.
>
> While I'm entirely in favor of this feature in general, I think this is
> the wrong way to approach it.  It will end up like the CREATE OR REPLACE
> support: We add it for a few commands in one release, for a few more
> commands in the next release, for almost all commands in the following
> release, and now we're still not done.
>
> If we're going to store object creation time, I think we should do it
> for all objects, stored in a separate catalog, like pg_depend or
> pg_description, keyed off classid, objectid.  And have a simple C
> function to call to update the information stored there.
>
> That would also make storing the modification time, which I'd ask for
> next, easier.
>

+1

Pavel
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Re: Proposal: Store "timestamptz" of database creation on "pg_database"

[Peter Eisentraut]

On 1/3/13 6:34 AM, Hannu Krosing wrote:
>>> If what you want is something close to current unix file time semantics
>>> (ctime, mtime, atime) then why not just create a function to look up
>>> these
>>> attributes on database directory and/or database files ?
>> Because too many things change those. Moving to a different tablespace,
>> a rewriting ALTER TABLE, etc.
> Can't we actually fix these to preserve file creation date like tar does
> and still keep
> unix file semantics ?

I don't think that would be a good idea, because various file system
tools might actually want to look at, say, the mtime to know what to
back up.  Also, none of those file attributes are the *creation* time,
so we wouldn't actually solve the original problem.



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] pg_retainxlog for inclusion in 9.3?

[Robert Haas]

On Tue, Jan 1, 2013 at 10:10 AM, Magnus Hagander  wrote:
> So, it turns out the reason I got no feedback on this tool, was that I
> forgot both to email about and to actually push the code to github :O
> So this is actually code that's almost half a year old and that I was
> supposed to submit for the first or second commitfest. Oops.
>
> So, the tool and a README for it right now can be found at
> https://github.com/mhagander/pg_retainxlog for the time being.
>
> The idea behind the tool is to plug a hole in the case when
> pg_receivexlog is used for log archiving, which is that you still need
> a "blocking" archive_command in order to make sure that files aren't
> recycled on the master. So for 9.2 you can do this with an
> archive_command that checks if the file has appeared properly on the
> slave - but that usually means you're back at requiring ssh
> connectivity between the machines, for example. Even though this
> information is actually avialable on the master...
>
> This can also be of use to pure replication scenarios, where you don't
> know how to tune wal_keep_segments, but using actual live feedback
> instead of guessing.
>
> When pg_retainxlog is used as an archive_command, it will check the
> pg_stat_replication view instead of checking the slave. It will then
> only return ok once the requested logfile has been replicated to the
> slave. By default it will look for a replication client named
> pg_receivexlog, but it supports overriding the query with anything -
> so you can say things like "needs to have arrived on at least two
> replication slaves before we consider it archived". Or if used instead
> of wal_keep_segmnets, needs to have arrived at *all* replication
> slaves.
>
> Is this a tool that people would like to see included in the general
> toolchain? If so, I'll reformat it to work in the general build
> environment and submit it for the last commitfest.
>
> (comments on the code itself are of course also welcome)

I would tend to vote for putting this in contrib rather than src/bin.
But apart from that I have no objection to the idea.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Proposal: Store "timestamptz" of database creation on "pg_database"

[Peter Eisentraut]

On 1/2/13 11:08 PM, Fabrízio de Royes Mello wrote:
> The attached patch add a new column into 'pg_database' called
> 'datcreated' to store the timestamp of database creation.
> 
> If this feature is approved I could extend it to add a column into
> 'pg_class' to store creation timestamp too.

While I'm entirely in favor of this feature in general, I think this is
the wrong way to approach it.  It will end up like the CREATE OR REPLACE
support: We add it for a few commands in one release, for a few more
commands in the next release, for almost all commands in the following
release, and now we're still not done.

If we're going to store object creation time, I think we should do it
for all objects, stored in a separate catalog, like pg_depend or
pg_description, keyed off classid, objectid.  And have a simple C
function to call to update the information stored there.

That would also make storing the modification time, which I'd ask for
next, easier.



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Re: Proposal: Store "timestamptz" of database creation on "pg_database"

[Robert Haas]

On Thu, Jan 3, 2013 at 8:46 AM, Hannu Krosing  wrote:
> How is "what does database creation date mean?" a different question ?
>
> It is same question as :
>
> what is the creation date of db when I create a replica of my database from
> backup?
>
> does it depend on how I restore my replica ?
>
> can I restore it from pg_dump and still have same creation date ?
>
> etc. etc.

I think you (and Tom) are doing an excellent job of making a simple
problem seem complicated.  Suppose a man comes walking out of the
desert looking exhausted and collapses on our front doorstep,
muttering, in a semi-conscious state, the single word "water".   Now
this is a somewhat incoherent utterance, so there are several
objections that might be raised:

- It is not clear what the man wants done with the water.
- The amount of water to be provided is unspecified.
- Does he want tap water, bottled water, or club soda?
- Furthermore, if we do give him water, he might go on to ask for a
few crackers and a phone call; we could end up spending the whole
morning on this.
- In a situation of extreme thirst, a solution involving a proper
electrolyte balance would likely be superior to plain water.

Of course, these objections miss the point.  Even an imperfect
solution will be better than no solution at all.  And it is very
likely that if we simply provide whatever hydrating agent lies closest
to hand, we'll get full marks.

Similarly, in the present situation, I believe that there is little
reason to suppose that the simplest possible implementation of this
feature won't resolve the overwhelming majority of the needs that
people have.  We have many features about which users might raise the
same kinds of questions that you are raising about this one, and they
do, and those questions are perfectly valid.  But they are not reasons
to remove those features, and the questions you raise are not reasons
to avoid having this one.  They are simply things that must be
documented and explained, just as we need to do with every other
feature we ship.  And if someone is not perfectly happy with the
design, it won't be the first time for that, either.  It does not mean
that it's worse than not having anything.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] missing rename support

[Ali Dar]

On Sat, Dec 3, 2011 at 4:46 PM, Peter Eisentraut  wrote:
> I noticed the following object types don't have support for an ALTER ...
> RENAME command:
>
> DOMAIN (but ALTER TYPE works)
> FOREIGN DATA WRAPPER
> OPERATOR
> RULE
> SERVER
>
> Are there any restrictions why these couldn't be added?

> I don't think so.  There's no ALTER RULE command; should we add one
(matching ALTER TRIGGER) or make this part of ALTER TABLE?  I don't
think constraints can be renamed either, which should probably be
addressed along with rules.

> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company

Find attached an initial patch for ALTER RENAME RULE feature. Please
note that it does not have any documentation yet.


Alter_Rule_Rename.patch
Description: Binary data

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Re: Proposal: Store "timestamptz" of database creation on "pg_database"

[Hannu Krosing]

On 01/03/2013 02:42 PM, Stephen Frost wrote:

* Hannu Krosing (ha...@krosing.net) wrote:

But then some customer comes and wants it to mean "when was this
replica database created" ?

That's an entirely different question, imv, than what we're talking
about.

I'm not saying that it won't be asked, but as it's a different question,
we can look to answer it in a different way.


How is "what does database creation date mean?" a different question ?

It is same question as :

what is the creation date of db when I create a replica of my database 
from backup?


does it depend on how I restore my replica ?

can I restore it from pg_dump and still have same creation date ?

etc. etc.


Hannu







--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Re: Proposal: Store "timestamptz" of database creation on "pg_database"

[Stephen Frost]

* Hannu Krosing (ha...@krosing.net) wrote:
> But then some customer comes and wants it to mean "when was this
> replica database created" ?

That's an entirely different question, imv, than what we're talking
about.

I'm not saying that it won't be asked, but as it's a different question,
we can look to answer it in a different way.

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] Proposal: Store "timestamptz" of database creation on "pg_database"

[Stephen Frost]

* Fabrízio de Royes Mello (fabriziome...@gmail.com) wrote:
> On Thu, Jan 3, 2013 at 11:13 AM, Stephen Frost  wrote:
> > Please use hard-tabs (not spaces) and the column should come before the
> > variable length records (see the comment in pg_database.h).
> 
> You all right... I fixed it in attached patch.

You also need to fix the Anum_* values to match what's in the struct
definition now..

I'd recommend that you look over the code more closely and ensure that
you're ordering everything correctly throughout and that it all makes
sense..

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] Re: Proposal: Store "timestamptz" of database creation on "pg_database"

[Hannu Krosing]

On 01/03/2013 02:17 PM, Stephen Frost wrote:

* Hannu Krosing (ha...@krosing.net) wrote:

Can't we actually fix these to preserve file creation date like tar
does and still keep
unix file semantics ?

I'm not sure that I really see the advantage to trying to use the
filesystem to keep this information for us..?
If we would treat "database" as a file in this case then it would give 
us pre-defined meaning :)

So it is as about agreeing on what we actually want this "create time"
mean opening a can of worms as tom predicted ?

I agree that we need to hash out what, exactly, the values mean, but I
don't think that's a terribly difficult thing to do.


For example, how would this work in replication context ?

If it's stored in the database catalogs, this is clear- it's replicated
just like the catalog, and then you don't have to worry about trying to
ensure that the file creation timestamp in the filesystem is right...
But then some customer comes and wants it to mean "when was this replica 
database created" ?

Thanks,

Stephen




--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Proposal: Store "timestamptz" of database creation on "pg_database"

[Fabrízio de Royes Mello]

On Thu, Jan 3, 2013 at 11:33 AM, Fabrízio de Royes Mello <
fabriziome...@gmail.com> wrote:
>
>
> On Thu, Jan 3, 2013 at 11:13 AM, Stephen Frost  wrote:
> >
> > * Fabrízio de Royes Mello (fabriziome...@gmail.com) wrote:
> > > The attached patch add a new column into 'pg_database' called
'datcreated'
> > > to store the timestamp of database creation.
> >
> > Please use hard-tabs (not spaces) and the column should come before the
> > variable length records (see the comment in pg_database.h).
> >
>
> You all right... I fixed it in attached patch.
>

Please... discard this patch... I make a mistake... soon I send the new one.

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Blog sobre TI: http://fabriziomello.blogspot.com
>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello

Re: [HACKERS] Proposal: Store "timestamptz" of database creation on "pg_database"

[Fabrízio de Royes Mello]

On Thu, Jan 3, 2013 at 11:13 AM, Stephen Frost  wrote:
>
> * Fabrízio de Royes Mello (fabriziome...@gmail.com) wrote:
> > The attached patch add a new column into 'pg_database' called
'datcreated'
> > to store the timestamp of database creation.
>
> Please use hard-tabs (not spaces) and the column should come before the
> variable length records (see the comment in pg_database.h).
>

You all right... I fixed it in attached patch.

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Blog sobre TI: http://fabriziomello.blogspot.com
>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello


pg_database_add_datcreated_column_v2.patch
Description: Binary data

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Re: Proposal: Store "timestamptz" of database creation on "pg_database"

[Stephen Frost]

* Hannu Krosing (ha...@krosing.net) wrote:
> Can't we actually fix these to preserve file creation date like tar
> does and still keep
> unix file semantics ?

I'm not sure that I really see the advantage to trying to use the
filesystem to keep this information for us..?

> So it is as about agreeing on what we actually want this "create time"
> mean opening a can of worms as tom predicted ?

I agree that we need to hash out what, exactly, the values mean, but I
don't think that's a terribly difficult thing to do.

> For example, how would this work in replication context ?

If it's stored in the database catalogs, this is clear- it's replicated
just like the catalog, and then you don't have to worry about trying to
ensure that the file creation timestamp in the filesystem is right...

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] Proposal: Store "timestamptz" of database creation on "pg_database"

[Stephen Frost]

* Hannu Krosing (ha...@krosing.net) wrote:
> If what you want is something close to current unix file time
> semantics (ctime, mtime, atime) then why not just create a function
> to look up these attributes on database directory and/or database
> files ?

Because, as noted before, those aren't always going to be correct.
Database files can be rewritten and recreated based on certain commands
(eg: CLUSTER).  Perhaps there's a fork that isn't, but that almost seems
like it's more painful to try and figure out than just hooking in with
the CREATE command.

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] Proposal: Store "timestamptz" of database creation on "pg_database"

[Stephen Frost]

* Fabrízio de Royes Mello (fabriziome...@gmail.com) wrote:
> The attached patch add a new column into 'pg_database' called 'datcreated'
> to store the timestamp of database creation.

Please use hard-tabs (not spaces) and the column should come before the
variable length records (see the comment in pg_database.h).

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] [PATCH] Make pg_basebackup configure and start standby [Review]

[Boszormenyi Zoltan]

2013-01-02 11:54 keltezéssel, Boszormenyi Zoltan írta:

2013-01-02 10:37 keltezéssel, Boszormenyi Zoltan írta:

2013-01-02 10:12 keltezéssel, Magnus Hagander írta:
On Wed, Jan 2, 2013 at 9:59 AM, Boszormenyi Zoltan > wrote:


2013-01-02 01:24 keltezéssel, Tom Lane írta:

Boszormenyi Zoltan mailto:z...@cybertec.at>> writes:

2013-01-01 17:18 keltezéssel, Magnus Hagander írta:

That way we can get around the whole need for changing memory
allocation across all the
frontends, no? Like the attached.

Sure it's simpler but then the consistent look of the code is lost.
What about the other patch to unify pg_malloc and friends?
Basically all client code boils down to
  fprintf(stderr, ...)
in different disguise in their error reporting, so that patch can
also be simplified but it seems that the atexit() - either 
explicitly
or hidden behind InitPostgresFrontend() - cannot be avoided.

Meh.  I find it seriously wrongheaded that something as minor as an
escape_quotes() function should get to dictate both malloc wrappers
and error recovery handling throughout every program that might use it.


Actually, the unification of pg_malloc and friends wasn't dictated
by this little code, it was just that pg_basebackup doesn't provide
a pg_malloc implementation (only pg_malloc0) that is used by
initdb's escape_quotes() function. Then I noticed how wide these
almost identical functions have spread into client apps already.

I would say this unification patch is completely orthogonal to
the patch in $SUBJECT. I will post it in a different thread if it's
wanted at all. The extra atexit() handler is not needed if a simple
fprintf(stderr, ...) error reporting is enough in all clients.
As far as I saw, all clients do exactly this but some of them hide
this behind #define's.


Please do keep that one separate - let's avoid unnecessary feature-creep, whether it's 
good or bad features.


I like Magnus' version a lot better than that idea.


OK, I will post the core patch building on his code.


Thanks.

A bigger issue that I notice with this code is that it's only correct in
backend-safe encodings, as the comment mentions.  If we're going to be
putting it into frontend programs, how safe is that going to be?

regards, tom lane


The question in a different form is: does PostgreSQL support
non-ASCII-safe encodings at all (or on the client side)? Forgive
my ignorance and enlighten me: how many such encodings
exist besides EBCDIC? Is UTF-16 non-ASCII-safe?


We do. See http://www.postgresql.org/docs/9.2/static/multibyte.html.

There are quite a few far-eastern encodings that aren't available as server 
encondings, and I believe it's all for this reason.


I see, thanks.

That said, do we need to care *in this specific case*? We use it in initdb to parse 
config strings, I believe. And we'd use it to parse a conninfo string in 
pg_basebackup, correct?


Correct.

Perhaps all we need to do is to clearly comment that it doesn't work with non-ascii 
safe encodings, or rename it to indicate that it's limited in this?


If you send a new patch with the function renamed accordingly, I will modify
my code to use it.


Attached is the quotes-v2 patch, the function is renamed and
the comment is modified plus the pg_basebackup v21 patch
that uses this function.


Rebased after pgtar.h was added. The quotes.c comment was modified
even more so it doesn't say this function is used for SQL string literals.

Best regards,
Zoltán Böszörményi

--
--
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de
 http://www.postgresql.at/

diff -durpN postgresql/src/bin/initdb/initdb.c postgresql.1/src/bin/initdb/initdb.c
--- postgresql/src/bin/initdb/initdb.c	2013-01-02 09:19:03.855521804 +0100
+++ postgresql.1/src/bin/initdb/initdb.c	2013-01-03 11:31:10.819564769 +0100
@@ -354,6 +354,18 @@ pg_strdup(const char *s)
 	return result;
 }
 
+static char *
+escape_quotes(const char *src)
+{
+	char *result = escape_single_quotes_ascii(src);
+	if (!result)
+	{
+		fprintf(stderr, _("%s: out of memory\n"), progname);
+		exit(1);
+	}
+	return result;
+}
+
 /*
  * make a copy of the array of lines, with token replaced by replacement
  * the first time it occurs on each line.
@@ -2415,35 +2427,6 @@ check_ok(void)
 	}
 }
 
-/*
- * Escape (by doubling) any single quotes or backslashes in given string
- *
- * Note: this is used to process both postgresql.conf entries and SQL
- * string literals.  Since postgresql.conf strings are defined to treat
- * backslashes as escapes, we have to double backslashes here.	Hence,
- * when u

Re: [HACKERS] Re: Proposal: Store "timestamptz" of database creation on "pg_database"

[Hannu Krosing]

On 01/03/2013 11:18 AM, Andres Freund wrote:

On 2013-01-03 11:03:17 +0100, Hannu Krosing wrote:

On 12/28/2012 03:14 AM, Stephen Frost wrote:
...

I agree that what I was suggesting would be possible to implement with
event triggers, but I see that as a rather advanced feature that most
users aren't going to understand or implement. At the same time, those
more novice users are likely to be looking for this kind of information-
being told "oh, well, you *could* have been collecting it all along if you
knew about event triggers" isn't a particularly satisfying answer. That's
my 2c on it. I agree that having the example in the docs would be nice-
examples are always good things to include.

If what you want is something close to current unix file time semantics
(ctime, mtime, atime) then why not just create a function to look up these
attributes on database directory and/or database files ?

Because too many things change those. Moving to a different tablespace,
a rewriting ALTER TABLE, etc.
Can't we actually fix these to preserve file creation date like tar does 
and still keep

unix file semantics ?

So it is as about agreeing on what we actually want this "create time"
mean opening a can of worms as tom predicted ?

For example, how would this work in replication context ?


Greetings,

Andres Freund

--
  Andres Freundhttp://www.2ndQuadrant.com/
  PostgreSQL Development, 24x7 Support, Training & Services






--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Can't setval() a sequence to return the first value

[Pavel Stehule]

Hello

postgres=# select setval('xx', 1, false);
 setval

  1
(1 row)

postgres=# select nextval('xx');
 nextval
-
   1
(1 row)


Regards

Pavel

2013/1/3 Hannu Krosing :
> Hi hackers.
>
> Is it by design that you can't setval(seq, value) a sequence to a value
> which returns the first value:
>
> See the sample below for better explanation:
>
> hannu=# create sequence s;
> CREATE SEQUENCE
> hannu=# select nextval('s');
>  nextval
> -
>1
> (1 row)
> hannu=# select setval('s',1);
>  setval
> 
>   1
> (1 row)
> hannu=# select nextval('s');
>  nextval
> -
>2
> (1 row)
> hannu=# select setval('s',0);
> ERROR:  setval: value 0 is out of bounds for sequence "s"
> (1..9223372036854775807)
>
> Should it not be possible to set sequence to one below the start value so
> that you can completely reset it ?
>
> 
> Hannu
>
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Can't setval() a sequence to return the first value

[Hannu Krosing]

Hi hackers.

Is it by design that you can't setval(seq, value) a sequence to a value 
which returns the first value:


See the sample below for better explanation:

hannu=# create sequence s;
CREATE SEQUENCE
hannu=# select nextval('s');
 nextval
-
   1
(1 row)
hannu=# select setval('s',1);
 setval

  1
(1 row)
hannu=# select nextval('s');
 nextval
-
   2
(1 row)
hannu=# select setval('s',0);
ERROR:  setval: value 0 is out of bounds for sequence "s" 
(1..9223372036854775807)


Should it not be possible to set sequence to one below the start value 
so that you can completely reset it ?



Hannu




--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Behaviour of bgworker with SIGHUP

[Guillaume Lelarge]

On Mon, 2012-12-31 at 17:44 -0300, Alvaro Herrera wrote:
> Alvaro Herrera wrote:
> > Guillaume Lelarge wrote:
> > > On Mon, 2012-12-31 at 11:03 -0300, Alvaro Herrera wrote:
> > 
> > > > I think this (have a config option, and have SIGHUP work as expected)
> > > > would be useful to demo in worker_spi, if you care to submit a patch.
> > > 
> > > Yeah, I would love too. Reading the code of worker_spi, we could add one
> > > or three parameters: a naptime, and the schemaname for both bgprocess.
> > > One would be enough or do you prefer all three?
> > 
> > I got no problem with three.
> 
> Actually, it occurs to me that it might be useful to demonstrate having
> the number of processes be configurable: so we could use just two
> settings, naptime and number of workers.  Have each worker just use a
> hardcoded schema, say "worker_spi_%d" or something like that.
> 

Here you go.

worker_spi.naptime is the naptime between two checks.
worker_spi.total_workers is the number of workers to launch at
postmaster start time. The first one can change with a sighup, the last
one obviously needs a restart.


-- 
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com
diff --git a/contrib/worker_spi/worker_spi.c b/contrib/worker_spi/worker_spi.c
index 6da747b..4b6de45 100644
--- a/contrib/worker_spi/worker_spi.c
+++ b/contrib/worker_spi/worker_spi.c
@@ -35,12 +35,16 @@
 #include "lib/stringinfo.h"
 #include "utils/builtins.h"
 #include "utils/snapmgr.h"
+#include "tcop/utility.h"
 
 PG_MODULE_MAGIC;
 
 void	_PG_init(void);
 
+static bool	got_sighup = false;
 static bool	got_sigterm = false;
+static int  worker_spi_naptime = 1;
+static int  worker_spi_total_workers = 2;
 
 
 typedef struct worktable
@@ -65,6 +69,7 @@ static void
 worker_spi_sighup(SIGNAL_ARGS)
 {
 	elog(LOG, "got sighup!");
+	got_sighup = true;
 	if (MyProc)
 		SetLatch(&MyProc->procLatch);
 }
@@ -176,13 +181,22 @@ worker_spi_main(void *main_arg)
 		 */
 		rc = WaitLatch(&MyProc->procLatch,
 	   WL_LATCH_SET | WL_TIMEOUT | WL_POSTMASTER_DEATH,
-	   1000L);
+	   worker_spi_naptime*1000L);
 		ResetLatch(&MyProc->procLatch);
 
 		/* emergency bailout if postmaster has died */
 		if (rc & WL_POSTMASTER_DEATH)
 			proc_exit(1);
 
+		/*
+		 * In case of a sighup, just reload the configuration.
+		 */
+if (got_sighup)
+{
+got_sighup = false;
+ProcessConfigFile(PGC_SIGHUP);
+}
+
 		StartTransactionCommand();
 		SPI_connect();
 		PushActiveSnapshot(GetTransactionSnapshot());
@@ -225,11 +239,40 @@ _PG_init(void)
 {
 	BackgroundWorker	worker;
 	worktable		   *table;
+	unsigned inti;
+	charname[20];
+
+	/* get the configuration */
+	DefineCustomIntVariable("worker_spi.naptime",
+"Duration between each check (in seconds).",
+NULL,
+&worker_spi_naptime,
+1,
+1,
+INT_MAX,
+PGC_SIGHUP,
+0,
+NULL,
+NULL,
+NULL);
+	DefineCustomIntVariable("worker_spi.total_workers",
+"Number of workers.",
+NULL,
+&worker_spi_total_workers,
+2,
+1,
+100,
+PGC_POSTMASTER,
+0,
+NULL,
+NULL,
+NULL);
 
 	/* register the worker processes.  These values are common for both */
 	worker.bgw_flags = BGWORKER_SHMEM_ACCESS |
 		BGWORKER_BACKEND_DATABASE_CONNECTION;
 	worker.bgw_start_time = BgWorkerStart_RecoveryFinished;
+	worker.bgw_restart_time = BGW_NEVER_RESTART;
 	worker.bgw_main = worker_spi_main;
 	worker.bgw_sighup = worker_spi_sighup;
 	worker.bgw_sigterm = worker_spi_sigterm;
@@ -242,22 +285,17 @@ _PG_init(void)
 	 * memory in the child process; and if we fork and then exec, the exec'd
 	 * process will run this code again, and so the memory is also valid there.
 	 */
-	table = palloc(sizeof(worktable));
-	table->schema = pstrdup("schema1");
-	table->name = pstrdup("counted");
+	for (i = 1; i <= worker_spi_total_workers; i++)
+	{
+		sprintf(name, "worker %d", i);
+		worker.bgw_name = pstrdup(name);
 
-	worker.bgw_name = "SPI worker 1";
-	worker.bgw_restart_time = BGW_NEVER_RESTART;
-	worker.bgw_main_arg = (void *) table;
-	RegisterBackgroundWorker(&worker);
-
-	/* Values for the second worker */
-	table = palloc(sizeof(worktable));
-	table->schema = pstrdup("our schema2");
-	table->name = pstrdup("counted rows");
-
-	worker.bgw_name = "SPI worker 2";
-	worker.bgw_restart_time = 2;
-	worker.bgw_main_arg = (void *) table;
-	RegisterBackgroundWorker(&worker);
+		table = palloc(sizeof(worktable));
+		sprintf(name, "schema%d", i);
+		table->schema = pstrdup(name);
+		table->name = pstrdup("counted");
+		worker.bgw_main_arg = (void *) table;
+
+		RegisterBackgroundWorker(&worker);
+	}
 }

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Proposal: Store "timestamptz" of database creation on "pg_database"

[Hannu Krosing]

On 01/03/2013 05:04 AM, Robert Haas wrote:

O
Yeah, I don't think this is really a problem.  I would expect the psql
support for this feature to be not a whole lot more complicated than
that.  Sure, it might be more than 5 lines of raw code if it requires
an additional version of some query for which we're currently using
the same version for both PG93 and PG92, but it's hardly fair to cite
that as an argument for not doing this.  Such changes are almost
entirely boilerplate.
Here is a pl/python function which gives you "the real" database 
creation time.


CREATE OR REPLACE FUNCTION database_create_ts(INOUT dbname text, OUT 
ctime timestamp)

RETURNS SETOF RECORD
LANGUAGE plpythonu AS
$$
import os, time
res = plpy.execute("""select datname,
 current_setting('data_directory') ddir,
 oid as dboid
from pg_database where datname like '%s';""" % 
dbname)

for row in res:
dbpath = '%(ddir)s/base/%(dboid)s' % row
stat = os.stat(dbpath)
yield row['datname'], '%04d-%02d-%02d %02d:%02d:%02d+00' % 
time.gmtime(stat.st_ctime)[:6]

$$;

SELECT * FROM database_create_ts('template%');

--
Hannu





--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Proposal: Store "timestamptz" of database creation on "pg_database"

[Bernd Helmle]

--On 2. Januar 2013 23:04:43 -0500 Robert Haas  
wrote:



TBH, I don't think anyone has any business changing the creation
timestamp.  Ever.  For me, the fact that pg_dump wouldn't preserve
this information would be a feature, not a bug.  I mostly meant to
point out that someone could bypass it if they cared enough, not to
recommend it.  Honestly, I'd probably *rather* store this information
someplace where it couldn't be changed via SQL *at all*.  But I don't
think we have such a place, so I'm happy enough to store it in the
catalogs, with the associated risks of catalog hackery that entails.


This is exactly what Informix does, it stores creation or modification 
dates of a table in its system catalog (systables.created, to be specific). 
Any export/import of tables doesn't preserve the dates, if you restore a 
database (or table), the creation date is adjusted. I'm not aware of any 
SQL interface to influence this.


--
Thanks

Bernd


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Proposal: Store "timestamptz" of database creation on "pg_database"

[Pavel Stehule]

2013/1/3 Hannu Krosing :
> On 12/28/2012 03:14 AM, Stephen Frost wrote:
> ...
>>
>> I agree that what I was suggesting would be possible to implement with
>> event triggers, but I see that as a rather advanced feature that most users
>> aren't going to understand or implement. At the same time, those more novice
>> users are likely to be looking for this kind of information- being told "oh,
>> well, you *could* have been collecting it all along if you knew about event
>> triggers" isn't a particularly satisfying answer. That's my 2c on it. I
>> agree that having the example in the docs would be nice- examples are always
>> good things to include.
>
> If what you want is something close to current unix file time semantics
> (ctime, mtime, atime) then why not just create a function to look up these
> attributes on database directory and/or database files ?

Implementation of ctime, mtime, atime will have little bit higher
impact than just creation time - and these values should be moved to
statistics instead bloated pg_class.

You cannot use a filesystem data, because some requests are solved by
cache not by filesystem.

I had to emulate MySQL fields - and this was a first implementation,
but totally useles - now we have a solution based on enhancing pg_stat
and it works as expected

Regards

Pavel


>
> 
> Hannu
>
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Re: Proposal: Store "timestamptz" of database creation on "pg_database"

[Andres Freund]

On 2013-01-03 11:03:17 +0100, Hannu Krosing wrote:
> On 12/28/2012 03:14 AM, Stephen Frost wrote:
> ...
> >I agree that what I was suggesting would be possible to implement with
> >event triggers, but I see that as a rather advanced feature that most
> >users aren't going to understand or implement. At the same time, those
> >more novice users are likely to be looking for this kind of information-
> >being told "oh, well, you *could* have been collecting it all along if you
> >knew about event triggers" isn't a particularly satisfying answer. That's
> >my 2c on it. I agree that having the example in the docs would be nice-
> >examples are always good things to include.
> If what you want is something close to current unix file time semantics
> (ctime, mtime, atime) then why not just create a function to look up these
> attributes on database directory and/or database files ?

Because too many things change those. Moving to a different tablespace,
a rewriting ALTER TABLE, etc.

Greetings,

Andres Freund

--
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Proposal: Store "timestamptz" of database creation on "pg_database"

[Hannu Krosing]

On 12/28/2012 03:14 AM, Stephen Frost wrote:
...
I agree that what I was suggesting would be possible to implement with 
event triggers, but I see that as a rather advanced feature that most 
users aren't going to understand or implement. At the same time, those 
more novice users are likely to be looking for this kind of 
information- being told "oh, well, you *could* have been collecting it 
all along if you knew about event triggers" isn't a particularly 
satisfying answer. That's my 2c on it. I agree that having the example 
in the docs would be nice- examples are always good things to include. 
If what you want is something close to current unix file time semantics 
(ctime, mtime, atime) then why not just create a function to look up 
these attributes on database directory and/or database files ?



Hannu



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Proposal: Store "timestamptz" of database creation on "pg_database"

[Pavel Stehule]

2013/1/3 Stephen Frost :
> * Robert Haas (robertmh...@gmail.com) wrote:
>> Well, IMHO, there is no need for any syntax change at all.  CREATE
>> TABLE and CREATE DATABASE should just record the creation time
>> somewhere, and that's all.  If you dump-and-reload, the creation time
>> changes.  Deal with it, or hack your catalogs if you really care that
>> much.
>
> I'd be alright with this also, tbh.  Not preserving such information
> across pg_dump's wouldn't really be all *that* much of a loss.
>
> As for hacking at the catalogs, I do find that a rather terrible
> recommendation, ever.  I'm currently trying to convince people at $work
> that hacking at pg_database to modify datallowconns is really not a
> good or ideal solution (and requires a lot more people to have
> superuser rights than really should, which is practically no one, imo).
> Annoyingly, we don't seem to have a way to ALTER DATABASE to set that
> value, although I *think* 'connection limit = 0' might be good enough.
>
>> I find the suggestion of using event triggers for this to miss the
>> point almost completely.  At least in my case, the time when you
>> really wish you had some timestamps is when you get dropped into a
>> customer environment and need to do forensics.  The customer will not
>> have installed the convenient package of event triggers at database
>> bootstrap time.  Their environment will likely be poorly configured
>> and completely undocumented; that's why you're doing forensics, isn't
>> it?
>
> Exactly, that's what I was trying to get at upstream.
>
>> I know this has been discussed and rejected before, but I find that
>> rejection to be wrong-headed.  I have repeatedly been asked, with
>> levels of exasperation ranging from mild to homicidal, why we don't
>> have this feature, and I have no good answer.  If it were somehow
>> difficult to record this or likely to produce a lot of overhead, that
>> would be one thing.  But it isn't.  It's probably a hundred-line
>> patch, and AFAICS the overhead would be miniscule.
>
> +1

+1

yes, this task can be simply solved by EVENT TRIGGERS, but native
implementation can carry some unification - and time of creation is
basic attribute that I would to see everywhere. And I am not alone

regards

Pavel Stehule

>
> Thanks,
>
> Stephen


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers