Hi
I'm looking for advice on the best way to index a table that is defined as:
create table uuid.master(id uuid, parent uuid references
uuid.master(id), type_id smallint, primary key(id));
Besides the primary key, I have these two indices on the table too:
CREATE INDEX master_parent_idx ON
Hello,
I need to log access to certain data in database in some log (I prefer
to have that both in syslog and table in database), and I find it easy
to write to syslog, but I can't solve the problem of writing this to
database table.
If this protected data is read only using postgres
On Wed, Jan 25, 2012 at 11:54 AM, Jason Armstrong j...@riverdrums.com wrote:
Hi
I'm looking for advice on the best way to index a table that is defined as:
create table uuid.master(id uuid, parent uuid references
uuid.master(id), type_id smallint, primary key(id));
Besides the primary key,
OK, thanks for replys. To sum up, this is what I now consider best practice:
CREATE schema schema1;
CREATE schema schema2;
CREATE SEQUENCE global_seq; --in public schema
CREATE TABLE tbl (ID bigint default nextval('global_seq') primary key,foo
varchar,bar varchar); --in public schema
CREATE
Hi folks,
We run a nightly cronjob on secondary database machine (9.0.6) to
produce an abbreviated dataset for developer laptops. The procedure is roughly
as follows:
* pg_dump production db into scratch db on secondary box
* one big
hi,
Question is basically in the title, but let's show some example:
$ begin;
BEGIN
*$ set timezone = 'EST';
SET
*$ select now(), extract(epoch from now()), extract(epoch from now() at time
zone 'UTC');
now │date_part │date_part
hubert depesz lubaczewski dep...@depesz.com writes:
Why aren't the 3rd date_parts the same in both cases? I mean - I see that they
are adjusted due to timezone, but why is it happening?
Given a timestamp without time zone, timestamp_part('epoch') assumes
that it is in session timezone, and
On Wed, Jan 25, 2012 at 10:35:47AM -0500, Tom Lane wrote:
hubert depesz lubaczewski dep...@depesz.com writes:
Why aren't the 3rd date_parts the same in both cases? I mean - I see that
they
are adjusted due to timezone, but why is it happening?
Given a timestamp without time zone,
On Wednesday, January 25, 2012 7:22:25 am hubert depesz lubaczewski wrote:
hi,
Question is basically in the title, but let's show some example:
$ begin;
BEGIN
*$ set timezone = 'EST';
SET
*$ select now(), extract(epoch from now()), extract(epoch from now() at
time zone 'UTC'); now
hubert depesz lubaczewski dep...@depesz.com writes:
how can I then have immutable epoch for given point in time?
What do you consider to be a given point in time? It seems like
you have not thought through what effects the timezone setting has
on your concept of now, or at least you have not
On Wednesday, January 25, 2012 7:37:27 am hubert depesz lubaczewski wrote:
On Wed, Jan 25, 2012 at 10:35:47AM -0500, Tom Lane wrote:
hubert depesz lubaczewski dep...@depesz.com writes:
Why aren't the 3rd date_parts the same in both cases? I mean - I see
that they are adjusted due to
On Wed, Jan 25, 2012 at 07:37:44AM -0800, Adrian Klaver wrote:
Its not the extract part but the at time zone part see:
http://www.postgresql.org/docs/9.0/interactive/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT
not sure what you mean - timestamptz at time zone converts to timestamp
On Wed, Jan 25, 2012 at 10:43:59AM -0500, Tom Lane wrote:
hubert depesz lubaczewski dep...@depesz.com writes:
how can I then have immutable epoch for given point in time?
What do you consider to be a given point in time? It seems like
you have not thought through what effects the timezone
On Wed, Jan 25, 2012 at 07:44:14AM -0800, Adrian Klaver wrote:
I thought that this is what I will achieve with extract(epoch from now()
at time zone 'UTC') but clearly it doesn't work.
So what options do I have?
Isn't extract(epoch from now()) getting what you want?
you can't make index
On Wednesday, January 25, 2012 7:44:44 am hubert depesz lubaczewski wrote:
On Wed, Jan 25, 2012 at 07:37:44AM -0800, Adrian Klaver wrote:
Its not the extract part but the at time zone part see:
http://www.postgresql.org/docs/9.0/interactive/functions-datetime.html#FU
On Wed, Jan 25, 2012 at 08:06:42AM -0800, Adrian Klaver wrote:
And therein lies the problem:) Per Toms comment, extract sees these
timestamps
without timezones and assumes they are local time and rotates them back to
UTC.
i know about it.
but - given the fact that date_part(, timestamp) is
On Wednesday, January 25, 2012 7:48:34 am hubert depesz lubaczewski wrote:
On Wed, Jan 25, 2012 at 07:44:14AM -0800, Adrian Klaver wrote:
I thought that this is what I will achieve with extract(epoch from
now() at time zone 'UTC') but clearly it doesn't work.
So what options do I have?
On Wed, Jan 25, 2012 at 5:54 AM, Jason Armstrong j...@riverdrums.com wrote:
Hi
I'm looking for advice on the best way to index a table that is defined as:
create table uuid.master(id uuid, parent uuid references
uuid.master(id), type_id smallint, primary key(id));
Besides the primary key,
On Wed, Jan 25, 2012 at 08:10:19AM -0800, Adrian Klaver wrote:
On Wednesday, January 25, 2012 7:48:34 am hubert depesz lubaczewski wrote:
On Wed, Jan 25, 2012 at 07:44:14AM -0800, Adrian Klaver wrote:
I thought that this is what I will achieve with extract(epoch from
now() at time zone
On Wednesday, January 25, 2012 8:08:37 am hubert depesz lubaczewski wrote:
On Wed, Jan 25, 2012 at 08:06:42AM -0800, Adrian Klaver wrote:
And therein lies the problem:) Per Toms comment, extract sees these
timestamps without timezones and assumes they are local time and rotates
them back to
On Wed, Jan 25, 2012 at 08:22:26AM -0800, Adrian Klaver wrote:
The issue seems to be the definition of same arguments. Since epoch is
anchored
at 1970-01-01 00:00:00 UTC the timestamp passed to extract need to be
normalized
to UTC. Once a timestamp is in UTC then the epoch can be
hubert depesz lubaczewski dep...@depesz.com writes:
anyway - the point is that in \df date_part(, timestamp) says it's
immutable, while it is not.
Hmm, you're right. I thought we'd fixed that way back when, but
obviously not. Or maybe the current behavior of the epoch case
postdates that.
On Wednesday, January 25, 2012 8:30:17 am hubert depesz lubaczewski wrote:
On Wed, Jan 25, 2012 at 08:22:26AM -0800, Adrian Klaver wrote:
The issue seems to be the definition of same arguments. Since epoch is
anchored at 1970-01-01 00:00:00 UTC the timestamp passed to extract need
to be
On Wed, Jan 25, 2012 at 08:54:44AM -0800, Adrian Klaver wrote:
Personally, I think that extract(epoch from timestamp) should assume
that the timestamp is UTC.
What if it isn't?
then you can always correct it with at time zone 'some specific time
zone'
but you can't correct it the other
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
If this protected data is read only using postgres function , and if in
the same function I add something like insert into log_table (blah blah
blah), somebody could simply do
begin;
select * from access_function(); /* assuming
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
i just needed to round some numbers down to 4 decimal places but a quick
search
indicated that postgresql doesn't support all of the rounding methods so i had
to write this dreadful function:
create or replace function
Well,
You could use dblink in your access_function() to log it...
But maybe it would be better to reorganise security on the way that users
who do not need to have access to some data - simply do not have it
(instead of to give them data and latter check log to confirm they have
taken it...)
In response to Misa Simic misa.si...@gmail.com:
But maybe it would be better to reorganise security on the way that users
who do not need to have access to some data - simply do not have it
(instead of to give them data and latter check log to confirm they have
taken it...)
In many cases
In response to Misa Simic misa.si...@gmail.com:
But maybe it would be better to reorganise security on the way that users
who do not need to have access to some data - simply do not have it
(instead of to give them data and latter check log to confirm they have
taken it...)
In many cases
Thanks Bill,
Make sense... db_link is probably then solution... Everything depends on
concrete problem...
But I still think security should be reconsidered (I would use db_link just
in case there is no other options - if we must let users to have direct
access to DB)... I mean, in that case
On 01/25/12 18:38, Greg Sabino Mullane napisa:
You would need to break out of the transaction somehow within that
function and make a new call to the database, for example using dblink
or plperlu. I've done the latter before and it wasn't too painful.
The general idea is:
- ---
$dbh =
On 01/25/12 20:02, Misa Simic napisa:
Thanks Bill,
Make sense... db_link is probably then solution... Everything depends on
concrete problem...
But I still think security should be reconsidered (I would use db_link
just in case there is no other options - if we must let users to have
direct
On 01/25/2012 08:57 AM, hubert depesz lubaczewski wrote:
On Wed, Jan 25, 2012 at 08:54:44AM -0800, Adrian Klaver wrote:
Personally, I think that extract(epoch from timestamp) should assume
that the timestamp is UTC.
What if it isn't?
then you can always correct it with at time zone 'some
On Wed, Jan 25, 2012 at 02:07:40PM -0800, Adrian Klaver wrote:
Finally dawned on me. When you use 'at time zone' on a timestamp
with tz it strips the tz which then allows the value to be indexed
because:
-[ RECORD 5
We have it too.
I think it might be samba related, or just some SNMP software running.
I had indentifed the root of the situation some years ago, do not remember it
now.
It is 100% harmless.
On Τρι 24 Ιαν 2012 10:26:25 Florian Weimer wrote:
* David Johnston:
Immediately upon starting the
I was wondering if something like this could be possible:
SELECT * FROM table1
WHERE a1 = ... AND a2 = ... AND b1 = ... AND b2 = ...
The first thing to note is that the WHERE clause is dynamic, as in it can
contain more parameters or lesser parameters.
But, what I want to try accomplish
Is it possible (in a plperl function) to know if an update is from a
foreign key cascade, rather than just a user submitted update
statement?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
On Wednesday, January 25, 2012 2:46:39 pm hubert depesz lubaczewski wrote:
On Wed, Jan 25, 2012 at 02:07:40PM -0800, Adrian Klaver wrote:
Finally dawned on me. When you use 'at time zone' on a timestamp
with tz it strips the tz which then allows the value to be indexed
because:
-[
38 matches
Mail list logo
