Re: [HACKERS] Partitioned tables constraint_exclusion
Jim Nasby wrote: See Simon's reply... timestamptz math is *not* IMMUTABLE, because sessions are free to change their timezone at any time. I bet you can get some invalid results using that function with a clever test case. I'm pretty sure it could easily be broken. But to make it easier for me, I know that the reporting system connects, runs the query, and disconnects. So I'm so far safe using my current system. If the system had persistent connections and changed timezones a lot, it might however cause problems. Its been the only way that I could get it to be smart enough to not use the tables outside its range. With the tables growing 2+ million rows a day, approaching 1 billion rows, its helps performance a lot. This works at least until the ongoing discussion of partitioned tables hopefully improves things in this area. On Mar 26, 2007, at 3:48 PM, Weslee Bilodeau wrote: This works - CREATE FUNCTION now_interval( interval ) RETURNS timestamptz IMMUTABLE STRICT AS $$ SELECT NOW() - $1 $$ LANGUAGE SQL ; SELECT count(*) FROM master WHERE var_ts now_interval( '1 month' ); This doesn't work - SELECT count(*) FROM master WHERE var_ts ( NOW() - '1 month'::interval ); This works for me, as the reporting system I know doesn't change timezones, and function cache doesn't last longer then the current select? But, its basically the exact same logic in both cases? Weslee ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) Weslee ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] Partitioned tables constraint_exclusion
I'm not sure if this is a bug, missing feature, misunderstanding on my part?
I checked the TODO list and couldn't find anything on it.
I currently have a 750 million row table, indexes are 10 GB, so trying
to partition it.
The basic -
constraint_exclusion + exact match = OK
constraint_exclusion + ( var + var )::case = Not OK
Weslee
I tried to break it down to a simple case -
(kid_200601 should never show up in the plan)
mytest=# create table master ( var_text text not null, var_ts timestamp
with time zone not null, unique ( var_ts ) );
NOTICE: CREATE TABLE / UNIQUE will create implicit index
master_var_ts_key for table master
CREATE TABLE
mytest=# create table kid_200601 ( check ( var_ts = '2006-01-01
00:00:00' AND var_ts '2006-02-01 00:00:00' ) ) inherits ( master ) ;
CREATE TABLE
mytest=# create table kid_200602 ( check ( var_ts = '2006-02-01
00:00:00' AND var_ts '2006-03-01 00:00:00' ) ) inherits ( master ) ;
CREATE TABLE
mytest=# create table kid_200603 ( check ( var_ts = '2006-03-01
00:00:00' AND var_ts '2006-04-01 00:00:00' ) ) inherits ( master ) ;
CREATE TABLE
mytest=# explain select count(*) from master where var_ts '2006-02-22
00:00:00' ;
QUERY PLAN
---
Aggregate (cost=71.94..71.95 rows=1 width=0)
- Append (cost=7.09..69.18 rows=1101 width=0)
- Bitmap Heap Scan on master (cost=7.09..21.68 rows=367 width=0)
Recheck Cond: (var_ts '2006-02-22
00:00:00+00'::timestamp with time zone)
- Bitmap Index Scan on master_var_ts_key
(cost=0.00..7.00 rows=367 width=0)
Index Cond: (var_ts '2006-02-22
00:00:00+00'::timestamp with time zone)
- Seq Scan on kid_200602 master (cost=0.00..23.75 rows=367
width=0)
Filter: (var_ts '2006-02-22 00:00:00+00'::timestamp
with time zone)
- Seq Scan on kid_200603 master (cost=0.00..23.75 rows=367
width=0)
Filter: (var_ts '2006-02-22 00:00:00+00'::timestamp
with time zone)
(10 rows)
mytest=# select now() ;
now
---
2007-03-26 16:02:29.360435+00
(1 row)
mytest=# explain select count(*) from master where var_ts ( now() - '1
month'::interval )::timestamptz ;
QUERY PLAN
--
Aggregate (cost=114.94..114.95 rows=1 width=0)
- Append (cost=7.10..111.27 rows=1468 width=0)
- Bitmap Heap Scan on master (cost=7.10..23.52 rows=367 width=0)
Recheck Cond: (var_ts (now() - '1 mon'::interval))
- Bitmap Index Scan on master_var_ts_key
(cost=0.00..7.01 rows=367 width=0)
Index Cond: (var_ts (now() - '1 mon'::interval))
- Seq Scan on kid_200601 master (cost=0.00..29.25 rows=367
width=0)
Filter: (var_ts (now() - '1 mon'::interval))
- Seq Scan on kid_200602 master (cost=0.00..29.25 rows=367
width=0)
Filter: (var_ts (now() - '1 mon'::interval))
- Seq Scan on kid_200603 master (cost=0.00..29.25 rows=367
width=0)
Filter: (var_ts (now() - '1 mon'::interval))
(12 rows)
mytest=# show constraint_exclusion ;
constraint_exclusion
--
on
(1 row)
mytest=# explain select count(*) from master where var_ts (
'2007-03-26 16:03:27.370627+00'::timestamptz - '1 month'::interval
)::timestamptz ;
QUERY PLAN
Aggregate (cost=105.77..105.78 rows=1 width=0)
- Append (cost=7.10..102.10 rows=1468 width=0)
- Bitmap Heap Scan on master (cost=7.10..22.60 rows=367 width=0)
Recheck Cond: (var_ts ('2007-03-26
16:03:27.370627+00'::timestamp with time zone - '1 mon'::interval))
- Bitmap Index Scan on master_var_ts_key
(cost=0.00..7.01 rows=367 width=0)
Index Cond: (var_ts ('2007-03-26
16:03:27.370627+00'::timestamp with time zone - '1 mon'::interval))
- Seq Scan on kid_200601 master (cost=0.00..26.50 rows=367
width=0)
Filter: (var_ts ('2007-03-26
16:03:27.370627+00'::timestamp with time zone - '1 mon'::interval))
- Seq Scan on kid_200602 master (cost=0.00..26.50 rows=367
width=0)
Filter: (var_ts ('2007-03-26
16:03:27.370627+00'::timestamp with time zone - '1 mon'::interval))
- Seq Scan on kid_200603 master (cost=0.00..26.50 rows=367
width=0)
Filter: (var_ts ('2007-03-26
16:03:27.370627+00'::timestamp with time zone - '1 mon'::interval))
(12 rows)
mytest=#
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an
Re: [HACKERS] Partitioned tables constraint_exclusion
Simon Riggs wrote:
On Mon, 2007-03-26 at 09:38 -0700, Weslee Bilodeau wrote:
mytest=# explain select count(*) from master where var_ts (
'2007-03-26 16:03:27.370627+00'::timestamptz - '1 month'::interval
)::timestamptz ;
If you're able to supply a constant value, why not subtract 1 month
before you submit the query?
AFAIK timestamptz arithmetic depends upon the current timezone which is
a STABLE value and so won't currently work with partitioning.
Having partitioning work with STABLE functions should be a TODO item if
it isn't already, but that requires some thought to implement and won't
happen for 8.3.
Mainly its because the value comes from a reporting system that has
minimal brains, it passes values it gets from the user directly into a
query.
IE, they enter '1 month', which I use to populate the interval value,
ts ( NOW() - $VALUE )
But, in the example I did a timestamp - interval, the exact date, not
NOW() - Still didn't work.
I'm guessing anything that has to think, math, etc is not valid for
constrain_exclusion?
Its not in the docs anywhere, so trying to isolate what can and can't be
done.
Weslee
mytest=# explain select count(*) from master where var_ts (
'2007-03-26 16:03:27.370627+00'::timestamptz + '1 second'::interval
)::timestamptz ;
QUERY PLAN
---
Aggregate (cost=105.77..105.78 rows=1 width=0)
- Append (cost=7.10..102.10 rows=1468 width=0)
- Bitmap Heap Scan on master (cost=7.10..22.60 rows=367 width=0)
Recheck Cond: (var_ts ('2007-03-26
16:03:27.370627+00'::timestamp with time zone + '00:00:01'::interval))
- Bitmap Index Scan on master_var_ts_key
(cost=0.00..7.01 rows=367 width=0)
Index Cond: (var_ts ('2007-03-26
16:03:27.370627+00'::timestamp with time zone + '00:00:01'::interval))
- Seq Scan on kid_200601 master (cost=0.00..26.50 rows=367
width=0)
Filter: (var_ts ('2007-03-26
16:03:27.370627+00'::timestamp with time zone + '00:00:01'::interval))
- Seq Scan on kid_200602 master (cost=0.00..26.50 rows=367
width=0)
Filter: (var_ts ('2007-03-26
16:03:27.370627+00'::timestamp with time zone + '00:00:01'::interval))
- Seq Scan on kid_200603 master (cost=0.00..26.50 rows=367
width=0)
Filter: (var_ts ('2007-03-26
16:03:27.370627+00'::timestamp with time zone + '00:00:01'::interval))
(12 rows)
mytest=# explain select count(*) from master where var_ts (
'2007-03-26 16:03:27.370627+00' ) ;
QUERY PLAN
--
Aggregate (cost=22.60..22.61 rows=1 width=0)
- Append (cost=7.09..21.68 rows=367 width=0)
- Bitmap Heap Scan on master (cost=7.09..21.68 rows=367 width=0)
Recheck Cond: (var_ts '2007-03-26
16:03:27.370627+00'::timestamp with time zone)
- Bitmap Index Scan on master_var_ts_key
(cost=0.00..7.00 rows=367 width=0)
Index Cond: (var_ts '2007-03-26
16:03:27.370627+00'::timestamp with time zone)
(6 rows)
mytest=#
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [HACKERS] Partitioned tables constraint_exclusion
Weslee Bilodeau wrote: Mainly its because the value comes from a reporting system that has minimal brains, it passes values it gets from the user directly into a query. IE, they enter '1 month', which I use to populate the interval value, ts ( NOW() - $VALUE ) But, in the example I did a timestamp - interval, the exact date, not NOW() - Still didn't work. I'm guessing anything that has to think, math, etc is not valid for constrain_exclusion? Its not in the docs anywhere, so trying to isolate what can and can't be done. This works - CREATE FUNCTION now_interval( interval ) RETURNS timestamptz IMMUTABLE STRICT AS $$ SELECT NOW() - $1 $$ LANGUAGE SQL ; SELECT count(*) FROM master WHERE var_ts now_interval( '1 month' ); This doesn't work - SELECT count(*) FROM master WHERE var_ts ( NOW() - '1 month'::interval ); This works for me, as the reporting system I know doesn't change timezones, and function cache doesn't last longer then the current select? But, its basically the exact same logic in both cases? Weslee ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Acclerating INSERT/UPDATE using UPS
Christopher Browne wrote: [EMAIL PROTECTED] (Hideyuki Kawashima) wrote: Joshua, I appreciate your quick informative reply. And, I also really appreciate your kind comments. Since I have joined this ML 3 hours ago, I tried to be polite and slightly nervous. But I was relieved by your message. Your idea sounds interesting; there is likely to be a considerable resistance to mechanisms, however, that would be likely to make PostgreSQL less robust. Be aware, also, that in a public forum like this, people are sometimes less gentle than Joshua. The fundamental trouble with this mechanism is that a power outage can instantly turn a database into crud. I can think of a few places where I don't care about the data if the power is lost - * Web-based session data A lot of web sites have separate session-only databases. If the database goes down, we have to truncate the tables anyways when it comes back up. * Reporting slaves We have replication slaves setup for internal (staff-only) reporting. Often a lot of temp and summary tables as well. If the data is lost, don't care. Its a reporting database. Re-syncing from another slave is no biggie for total data loss. Less a concern given the speed increase of the data it creates as well as data coming in from the master. * Front-end cache slaves Same type of situation as the reporting slaves. Basic front-end cache that replicates data to take load off the master. The slaves still have to do all the same insert/updates, but this means they'll spend less time in locks. They crash, point the apps to the master or another slave while you fix it. Weslee ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Getting the type Oid in a CREATE TYPE output function
Marko Kreen wrote:
On 10/16/06, Weslee Bilodeau [EMAIL PROTECTED] wrote:
Marko Kreen wrote:
The PGP functions happen to do it already - pgp_key_id().
Actually, Tom helped me realize I made a mistake, which I'm following
his suggestion. Not tying keys to OIDs which change when backup/restored.
Yeah, tying to oids is bad, you should link to names,
preferably schema-qualified. Anyway, that was just off-hand
suggestion.
[ snip nice description ]
I'm not sure if anyone else needs something like it, but it allows us to
transparently encrypt data directly in the tables. Minimum application
changes ('select enc_key' at connection) - the main requirement when
working on legacy code that needs to match todays security polices
quickly.
Some want row-level access control, then your scheme would not be enough.
Right now once I have it working, at least in the column-level keys I'm
looking for, I can worry about this.
My main concern at having multiple keys on the same column is the
ability to index it.
Right now you can index the encrypted values, however you can only do
exact matches to use the index.
IE -
select * from table where credit_card = '';
That would work, as it encrypts the '' then compares the encrypted
values in the index.
By having multiple keys, I can't do that.
Aside from breaking the index, you can't do any comparisons at all anymore.
Because '' is encrypted then compared.
If credit_card was encrypted using key1, and '' is encrypted using
key2, there can be no match, even if the unencrytped values match.
You'd have to decrypt everything and then compare, which is a large
performance hit.
Also, I like the ability to do this -
insert into table ( credit_card ) values ( '' ) ;
And its automatically encrypted. To support multiple keys, there has to
be a way to switch the keys.
Something like -
select enc_key( 'key1' );
insert into table ( credit_card ) values ( '' ) ;
select enc_key( 'key2' );
insert into table ( credit_card ) values ( '' ) ;
Its possible, just a bit messy.
The other thing is when doing -
select * from table;
If you only specified key1, you can view the '', but if you don't
specify the key2, what is displayed as it doesn't have the decryption
key for ''?
Right now it errors out if attempting to view a record you haven't
provided the key for. In this case you need to give all keys before you
can do the select.
Maybe it would be better to avoid combining the keys, instead have
hidden key in database and several user keys that grant access to that
key, thus you can revoke access from only some users.
I like more security. Its a combined two keys for the basic idea that -
The database itself does not have the key. If you hack into the server,
you will not be able to decrypt the values.
The application itself does not have the key.
If you hack the application and have access to enc_raw_read() (this
allows backups to work - selects the raw-encrypted values), you can't
decrypt the values.
Basically you call enc_key( 'application_key' ), the encrypt function is
called with a basic database_key || application_key, ensuring both parts
are always needed.
Thus backups are secure, and the application is a little more secure.
For things like storing credit cards, SSNs, personal information, this
allows us to say if you run off with the server, you can't do anything.
By storing the key in the database as you suggest and just giving access
to that key, any DBA can decrypt the values, and anyone who runs off
with a backup can as well. Your only securing the SQL-access, not the
backups. Which, more often its backups that are stolen, lost, etc.
But one thing I suggest strongly - use PGP encryption instead
of old encrypt()/decrypt(). PGP hides the data much better,
espacially in case of lot of small data with same key.
I may look into PGP once I get this part working.
Smaller steps for me, I'm new to postgres internal API. :)
Weslee
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] Getting the type Oid in a CREATE TYPE output function
Jim C. Nasby wrote:
On Tue, Oct 17, 2006 at 04:34:35PM +0300, Marko Kreen wrote:
I'm not sure if anyone else needs something like it, but it allows us to
transparently encrypt data directly in the tables. Minimum application
changes ('select enc_key' at connection) - the main requirement when
working on legacy code that needs to match todays security polices quickly.
Some want row-level access control, then your scheme would not be enough.
Maybe it would be better to avoid combining the keys, instead have
hidden key in database and several user keys that grant access to that
key, thus you can revoke access from only some users.
But one thing I suggest strongly - use PGP encryption instead
of old encrypt()/decrypt(). PGP hides the data much better,
espacially in case of lot of small data with same key.
Better yet, allow the user to plug in encryption modules. Different
people want different kinds of encryption. For example, I believe credit
card companies require AES192.
As its really just a type wrapper around automatically calling
pgcrypto's encrypt/decrypt functions, this should be very easy to do.
I currently default it to 'bf' (blowfish) I can just make the type
creator have an additional parameter that takes any method recognized by
the crypto library.
Weslee
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[HACKERS] Performance/Security question on caching function OIDs for a connection
I'm working on my custom encryption types (as outlined in another thread) and was curious of one potential performance hit. On the input and output functions for the new type, I lookup the encrypt/decrypt functions using - FuncnameGetCandidates( list_make1( makeString( decrypt ) ) Running through the list and getting the functions OID, then calling with OidFunctionCall3(). I'm concerned about the performance impact if say, importing thousands of rows in a COPY, doing the lookup for every row. To get around this I was thinking of doing the lookup once then caching the OIDs for the functions (a per-connection cache, not globally). I know the function can be dropped, I'm trapping the error so I'm not as concerned, but is it possible for someone to replace the same OID with another function? My thoughts were if the call failed, perform another lookup for the new OID, if it can't be found error out. But if the OID just points to a new function it may not generate an error and just return something unexpected. I'm not sure if its safe, or if I shouldn't even bother trying to cache for performance? I know it makes little difference when inserting one row as encryption takes the bulk of the time, but when you scale to thousands+ inserts at once? Weslee ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Getting the type Oid in a CREATE TYPE output function
Marko Kreen wrote:
On 10/12/06, Tom Lane [EMAIL PROTECTED] wrote:
Weslee Bilodeau [EMAIL PROTECTED] writes:
It works perfectly so long as I used the same key for all my custom
types. When I want a different key for each type though (so for
example,
encrypt credit cards with one key, addresses with another, etc) I
need a
way to tell them apart.
[ shrug... ] Seems like you should be putting the key ID into the
stored encrypted datums, then.
The PGP functions happen to do it already - pgp_key_id().
Actually, Tom helped me realize I made a mistake, which I'm following
his suggestion. Not tying keys to OIDs which change when backup/restored.
But actually for me, the key ID is not a PGP key.
When you create a new type you create a key ID, and map that key ID to
the OID attached to that type, it stores a hashed password value in a
little far-off place that it can use to ensure all inserts into that
same type are using the exact same encryption key (the key is actually
only half, the database has its own key. It combines the two keys to
encrypt/decrypt data).
Having the same column encrypted with 20 different keys is a bit of a
mess. So I just needed a way to ensure it was the same key with each
INSERT/UPDATE.
At login, you call -
SELECT enc_key( 'type', 'password' );
Returns OK if its the real key for that type, otherwise returns an
error with Invalid Key and refuses all read/writes (SELECT, INSERT,
UPDATE, etc) to those types, as it would if you never called enc_key()
in the first place.
If anyone else is curious I'll release the code once I have it actually
working. A few more days basically.
Allows things like -
-- Create the new type, just hides all the CREATE TYPE
-- and assigns the key to the type
select enc_type_new( 'enc_cardnumber', 'new_password' );
create table credit_card (
card_number enc_cardnumber not null,
card_name varchar(20) not null
);
insert into credit_card values ( '1234', 'test' ) ;
Login again -
select * from credit_card ;
ERROR: Please provide key
select enc_key( 'enc_cardnumber', 'new_password' );
SELECT * from credit_card ;
1234 | test
And yes, you can back it up. Map a user to be able to read/write raw
encrypted values and it allows backup/restores using pg_(dump|restore).
I'm not sure if anyone else needs something like it, but it allows us to
transparently encrypt data directly in the tables. Minimum application
changes ('select enc_key' at connection) - the main requirement when
working on legacy code that needs to match todays security polices quickly.
Weslee
---(end of broadcast)---
TIP 6: explain analyze is your friend
[HACKERS] Getting the type Oid in a CREATE TYPE output function ..
I'm trying to create a few new types, and based on the type in/out functions will operate a bit differently. For the input function finding the type Oid is easy - Oid our_type_oid = PG_GETARG_OID(1); For output though I'm having difficulty finding out the type Oid. I've tried using getBaseType, get_rel_type_id, and get_typ_typrelid. Maybe I'm using the options wrong? Or not looking in the right place? I'm only interested in getting it working on 8.1 or greater, so if it changes in older versions I'm not as concerned. Any help is appreciated. Weslee ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Getting the type Oid in a CREATE TYPE output function
Tom Lane wrote: Weslee Bilodeau [EMAIL PROTECTED] writes: I'm trying to create a few new types, and based on the type in/out functions will operate a bit differently. For the input function finding the type Oid is easy - Oid our_type_oid = PG_GETARG_OID(1); For output though I'm having difficulty finding out the type Oid. You can't, and if you could, relying on it would be a security hole in your function (somebody could invoke the function manually and pass it a false OID value). You have to put everything you need to know right into the Datum. I'm not as worried about them running it manually, since I want it to operate something like - select output_function( 'test'::text ); It would have the type OID for text. select output_function( 'test'::varchar ); It would have the type OID for varchar. I don't want them to tell me the OID they want, I just want to know what type the function was called with. Was it called as a varchar, text, my own type, bytea, etc ? Is this possible? A bit of what I'm trying to do - I'm creating an encrypted data type wrapped around pgcrypto. create table test ( test enctype ); insert into test values ( 'encrypt_me' ); The value in input is encrypted, then stored using byteain. The key used to encrypt it is based of the type. So I can use the same functions for 10 different CREATE TYPE statements. The output function descrypts the value, then hands it off to byteaout. It works perfectly so long as I used the same key for all my custom types. When I want a different key for each type though (so for example, encrypt credit cards with one key, addresses with another, etc) I need a way to tell them apart. The long way around is just create a new function for each type, but that seems messy since at least input can tell what type the input Datum is. Was hoping output can figure out the Datum type so I can decrypt it. Basically - create table test ( card enctype_card, addrress enctype_address ); Both types have different encryption keys. I know its best to encrypt in the application, and they can log the SQL on the server, or if your not using SSL it can be read, etc. Can't change the application to encrypt or use pgcrypto directly. Weslee ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
