Re: [GENERAL] could not read block... how could I identify/fix
Vick, Thank you very much. Yes, I just go ahead did what you said and all appears to be fine. On Thu, Mar 29, 2012 at 7:08 AM, Vick Khera vi...@khera.org wrote: On Wed, Mar 28, 2012 at 6:31 PM, Naoko Reeves naokoree...@gmail.com wrote: Do you think this should be the next step I might take? Could you give me an advice of how I could identify corrupted error. It seems to me that since you can successfully dump the table (I assume you validated the data was all there somehow), you should go ahead and dump your whole DB, delete the current one, create it again, then restore it from scratch. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Naoko Reeves http://www.anypossibility.com/
[GENERAL] could not read block... how could I identify/fix
There was a hardware crash. Since then INSERT to one table is failing with the following message: ERROR: could not read block 11857 of relation base/16396/3720450: read only 0 of 8192 bytes ERROR: could not read block 11805 of relation base/16396/3720450: read only 0 of 8192 bytes Similar error was fixed by doing re-indexing or identifying corrupted data by COPY command and remove the row etc. However, the issue hasn't been resolved yet after taking the following actions: REINDEXed entire table. It was successful. pg_dump was also successful then restore was successful. COPY corrupted table to file was successful with no error. Analyze was also successful with no error. Do you think this should be the next step I might take? Could you give me an advice of how I could identify corrupted error. Version: PostgreSQL 8.4.6 on i386-apple-darwin, compiled by GCC i686-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc. build 5370), 32-bit Thank you very much for your time in advance. -- Naoko
[GENERAL] invalid memory alloc request size 1765277700 Error Question
Version: PostgreSQL 8.4.6 on i386-apple-darwin, compiled by GCC i686-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc. build 5370), 32-bit There was an hardware crash. after that pg_dump failed with an error: ERROR: invalid memory alloc request size 1765277700 I searched archive and it indicates that this is data corruption. I have narrowed down to 7 records or so might be corrupted. However, something doesn't add up... Please see below: -- I have narrowed down the row SELECT * FROM table ORDER BY table_id OFFSET 526199 LIMIT 1 -- ERROR: invalid memory alloc request size 1765277700 -- I was able to view a few columns SELECT table_id, table_column1, table_column2 FROm table ORDER BY table_id OFFSET 526199 LIMIT 1 -- returns one row table_id = 12345 -- using that id to SELECT ALL. It shows fine...I was assuming this will give me same error... SELECT * FROM table WHERE table_id=12345 -- shows perfectly -- This also returns value just fine SELECT table_column3 FROM table WHERE table_id = 12345 -- However this returns an error SELECT table_column3 FROm table ORDER BY table_id OFFSET 526199 LIMIT 1 --error ERROR: invalid memory alloc request size 1765277700 To me it is as if it is accessing to different record.. I thought possibly primary key index might be corrupted.. so attempted REINDEX TABLE table; --ERROR: could not create unique index table_pkey --DETAIL: Table contains duplicated values. -- Now this returns 0 row... Not sure what I am doing wrong here... SELECT table_id FROM table GROUP BY table_id HAVING count(table_id) 1 If anyone could advice me why I am able to view record with primary key query but not with OFFSET? Do I consider these record as corrupted? If so deleting these records might resolve the error I am getting? Thank you very much for your time. -- Naoko
Re: [GENERAL] invalid memory alloc request size 1765277700 Error Question
Tom, Scott, Thank you very much for your advice and right questions that lead to me the solution - In summary, I was able to identify and delete all corrupted data with no data loss. Everything add up once I disabled index per Tom's advice. Here is the detail report: Review the data again in order to answer Scott's questions. Both shows that it use index: EXPLAIN SELECT * FROM table ORDER BY table_key OFFSET 526199 LIMIT 1; Limit (cost=42574.62..42574.70 rows=1 width=220) - Index Scan using table_pkey on table (cost=0.00..118098.91 rows=1459638 width=220) EXPLAIN SELECT * FROM table WHERE table_key = 304458; Index Scan using table_pkey on table (cost=0.00..7.38 rows=1 width=220) Index Cond: (table_key = 304458) The columns I was able to review were up to 5th columns, including anything after that would shows data corruptions. Column types are varied (varchar, boolean, text, integer) As soon as disabled Index per Tom's advice, everything became clear and make sense. SELECT table_key FROM table GROUP BY table_key HAVING count(table_key) 1 Returned 5 rows. Before disabling index, it returned 0 row. The I performed the following query to identify exactly what record to delete: SELECT * FROM table WHERE table_key=304458 -- error SELECT ctid FROM table WHERE table_key=304458 -- (2021,22) (17958,10) SELECT * FROM table WHERE ctid='(2021,22)' -- GOOD SELECT * FROM table WHERE ctid='(17958,10)' -- BAD ERROR: invalid memory alloc request size 1765277700 Removed all bad records and I was finally able to REINDEX pg_dump! Now everything looks VERY HAPPY. One thing I failed to report/notice earlier is that there were 2 type of errors: Some rows returned ERROR: invalid memory alloc request size 1765277700 Some rows returned ERROR: compressed data is corrupt Thank you so much again for all your help. - Naoko On Fri, Feb 24, 2012 at 1:25 PM, Scott Marlowe scott.marl...@gmail.comwrote: On Fri, Feb 24, 2012 at 4:01 AM, Naoko Reeves naokoree...@gmail.com wrote: -- I have narrowed down the row SELECT * FROM table ORDER BY table_id OFFSET 526199 LIMIT 1 -- ERROR: invalid memory alloc request size 1765277700 Are you certain that offset 526199 is using both the same query plan and doesn't produce this error? -- I was able to view a few columns SELECT table_id, table_column1, table_column2 FROm table ORDER BY table_id OFFSET 526199 LIMIT 1 -- returns one row table_id = 12345 -- using that id to SELECT ALL. It shows fine...I was assuming this will give me same error... SELECT * FROM table WHERE table_id=12345 -- shows perfectly -- This also returns value just fine SELECT table_column3 FROM table WHERE table_id = 12345 -- However this returns an error SELECT table_column3 FROm table ORDER BY table_id OFFSET 526199 LIMIT 1 --error ERROR: invalid memory alloc request size 1765277700 If you do select col_list from table where col_list is all cols except col3 do you get the error? What type of col is col3? -- Naoko Reeves http://www.anypossibility.com/
Re: [GENERAL] ERROR from pg_restore - From OS X to Ubuntu
Got it. Thank you very much! On Fri, Nov 4, 2011 at 2:06 AM, Albe Laurenz laurenz.a...@wien.gv.atwrote: Naoko Reeves wrote: I dumped from: [...] PostgreSQL 9.0.4 [...] to: [...] PostgreSQL 9.1.1 [...] During the restoration I got the following errors: ERROR: could not access file $libdir/targetinfo: No such file or directory ERROR: function public.pldbg_get_target_info(text, char) does not exist ERROR: could not access file $libdir/plugins/plugin_debugger: No such file or directory ERROR: function public.plpgsql_oid_debug(oid) does not exist My question is: 1. Is this safe to ignore? I don't recall using any of the function 2. If not, how can I install those missing libraries? I'd say it is safe to ignore. You must have the EDB debugger installed in the 9.0.4 database, but not in the destination database. You'll probably end up with some garbage (types etc.) in the public schema that you should remove if you ever want to install the EDB debugger in the target database, but other than that they should not bother you. Yours, Laurenz Albe -- Naoko Reeves http://www.anypossibility.com/
[GENERAL] ERROR from pg_restore - From OS X to Ubuntu
I dumped from: OS: OS X 10.5.8 pg version: PostgreSQL 9.0.4 on x86_64-apple-darwin, compiled by GCC i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5664), 64-bit Installation Method: EDB installer to: OS: Ubuntu 10.04.3 64bit pg version: PostgreSQL 9.1.1 on x86_64-pc-linux-gnu, compiled by gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 64-bit installation Method: apt-get install postgresql-9.1 postgresql-contrib-9.1 During the restoration I got the following errors: ERROR: could not access file $libdir/targetinfo: No such file or directory ERROR: function public.pldbg_get_target_info(text, char) does not exist ERROR: could not access file $libdir/plugins/plugin_debugger: No such file or directory ERROR: function public.plpgsql_oid_debug(oid) does not exist My question is: 1. Is this safe to ignore? I don't recall using any of the function 2. If not, how can I install those missing libraries? Thank you very much for your time in advance. Naoko
[GENERAL] Regex Query Index question
Hello,
I have query phone number in database as follows:
[123) 456-7890
(123) 456-7890
When I query like this:
SELECT * FROM phone
WHERE phone_number ~ ('^\[123' || '[-\s\)]{0,2}' || '456' || '[-\s\)]{0,2}'
|| '7890')
it use Index but if I query like this (notice first character is
open parenthesis instead of open square blacket ) :
SELECT phn_fk_key FROM phn WHERE
phn_fk_table = 14
AND llx_decrypt(phn_phone_enc) ~ ('^\(123' || '[-\s\)]{0,2}' || '456' ||
'[-\s\)]{0,2}' || '7890')
It doesn't use Index
co-worker suggested me to use chr(40) instead so I tried this:
SELECT phn_fk_key FROM phn WHERE
phn_fk_table = 14
AND llx_decrypt(phn_phone_enc) ~ ('^\' || chr(40) || '123' || '[-\s\)]{0,2}'
|| '456' || '[-\s\)]{0,2}' || '7890')
No success...
Also { and period doesn't seems to use index either but } ) [ ] $ #
works.
Could you guide me to right direction for me please?
Thank you very much for your time in advance.
Naoko Reeves
Re: [GENERAL] Regex Query Index question
Tom,
Thank you for your quick reply. Data start with (123 only returns 28
records where as phone number start with[123 returns 1.
Changed the data so that both will return 1 row.
One with (999 query takes about 30 seconds (30983ms) without index.
One with [999 take about 28 ms with index.
Yes, standard_conforming_strings is ON.
Also forgot to mentioned the version:
select version() PostgreSQL 8.4.6 on i386-apple-darwin, compiled by GCC
i686-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc. build 5370),
32-bit
Thank you very much for your time.
Naoko Reeves
On Thu, Aug 11, 2011 at 3:49 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Naoko Reeves naokoree...@gmail.com writes:
I have query phone number in database as follows:
[123) 456-7890
(123) 456-7890
When I query like this:
SELECT * FROM phone
WHERE phone_number ~ ('^\[123' || '[-\s\)]{0,2}' || '456' ||
'[-\s\)]{0,2}'
|| '7890')
it use Index but if I query like this (notice first character is
open parenthesis instead of open square blacket ) :
SELECT phn_fk_key FROM phn WHERE
phn_fk_table = 14
AND llx_decrypt(phn_phone_enc) ~ ('^\(123' || '[-\s\)]{0,2}' || '456' ||
'[-\s\)]{0,2}' || '7890')
It doesn't use Index
Probably it thinks the index isn't selective enough for that case. How
many entries are there starting with (123?
(BTW, I assume you've got standard_conforming_strings turned on, else
there are some other issues with these backslashes ...)
regards, tom lane
--
Naoko Reeves
http://www.anypossibility.com/
[GENERAL] Unable to kill local COPY
version: 8.3 The other day, my DB stop processing request. It still accepts connections but not processing those. So I quit all client connections from client yet those process still alive on SQL Server. I tried to Stop DB by issuing pt_ctl STOP -m fast but failed to shut down database. Next I issued pg_ctl stop -m immediate. I think this one killed all processes that were from client. The returning message says the server was stopped. So I issued ps -ef | grep postgres but master and 4 child processes were still there... 502 150 1 0 0:10.70 ?? 0:12.69 /Library/PostgresPlus/8.3/bin/postgres -D /data 502 165 150 0 0:03.71 ?? 0:05.15 postgres: logger process 502 171 150 0 0:44.15 ?? 1:06.28 postgres: writer process 502 72026 150 0 0:00.15 ?? 0:00.87 postgres: postgres mydata [local] VACUUM 502 81413 150 0 0:00.48 ?? 0:06.52 postgres: postgres mydata 127.0.0.1(56760) COPY I then tried to kill the following process 502 81413 150 0 0:00.48 ?? 0:06.52 postgres: postgres mydata 127.0.0.1(56760) COPY even with -s 15 I could not kill So what I end up doing was to hard reboot the machine... I have questions: 1) Did I have better choice than hard-reboot the machine? (soft reboot didn't work). 2) The COPY command I was tying to kill is local request. Is this the process postgres trying to write changes to the disk? If so, what could I have done to data? Thank you very much for your time.
[GENERAL] System Log is logging row number -1 is out of range 0..-1
version: PostgreSQL 8.3.8 on i386-apple-darwin8.11.1, compiled by GCC i686-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc. build 5370) We are using custom plugin to connect to postgresql. We reviewed OS (OS X 10.6.2 2.26 GHz Quad-Core Intel Xeon Meomory 6 GB) system log and noticed that the following lines are repeated in the log all day...(This log records NOTICE from sql as well) = Feb 17 20:43:25 LLX-4DA-2 [0x0-0xfd8fd8].com.4d.4d[73523]: row number -1 is out of range 0..-1 Feb 17 20:44:45: --- last message repeated 57 times --- == 4d is a 32 bit application (programming language + database engine which we don't use) we use to connect postgres. We are using custom plugin written in C by 3rd party to connect to postgres. I reviewed postgresql's log and this is not logged... however, when I google this error message, I saw once back in 2005 there was a discussion about it therefore I thought this might be then related to postgresql and hope that someone could direct me to right direction to correct this error... Thank you very much for your time in advance. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how do I disable automatic start on mac os x?
Try check this directory: Library: StartupItems: postgres-plus-8.xx there should be script to start automatically. On Mon, Jan 4, 2010 at 1:43 AM, Chris Withers ch...@simplistix.co.uk wrote: Hi All, I installed postgres from the enterprisedb-maintained one-click installer at http://www.postgresql.org/download/macosx. However, it starts automatically when the machine boots up, and I'd like it only to start when I manually start it. I've found an entry in launchd: $ sudo launchctl list | grep post com.edb.launchd.postgresql-8.4 ...but I can't find where this comes from: $ sudo find / -name com.edb.launchd.postgresql-8.4 $ Where do I find this entry to disable it? (and, perhaps showing my Mac-ignorance, is there a GUI for launchd configuration that I'm somehow missing?) cheers, Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sincerely, Naoko Reeves -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] column does not exist error
SELECT title FROM node WHERE type='client'; Would this work? -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Dave Coventry Sent: Wednesday, November 18, 2009 6:24 AM To: pgsql-general General Subject: [GENERAL] column does not exist error Tearing my hair out, can anyone see what I'm doing wrong? SELECT title FROM node WHERE type=client; ERROR: column client does not exist LINE 1: SELECT title FROM node WHERE type=client; Yet this works: SELECT type FROM node; type client client client client client (5 rows) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] does encrypt function support higher than basic ascii?
Hello,
I have the following statement and accent e doesn't seems to be decrypted
correctly.
select decrypt(encrypt('aéiou','foo','aes'),'foo','aes')
Could you tell me if there is an option for encoding or this function only
encrypt basic ascii?
Thank you very much for your time in advance.
Naoko
Re: [GENERAL] does encrypt function support higher than basic ascii?
I have tried:
select decrypt(encrypt((select convert('aéiou','UTF8',
'LATIN1')),'foo','aes'),'foo','aes')
select decrypt(encrypt((select convert('aéiou','UNICODE',
'LATIN1')),'foo','aes'),'foo','aes')
select decrypt(encrypt((select convert('aéiou','LATIN1',
'LATIN1')),'foo','aes'),'foo','aes')
but none of above seems to be resolving the issue...
-Original Message-
From: Richard Huxton [mailto:d...@archonet.com]
Sent: Tuesday, November 17, 2009 1:14 PM
To: Naoko Reeves
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] does encrypt function support higher than basic ascii?
Naoko Reeves wrote:
Hello,
I have the following statement and accent e doesn't seems to be
decrypted correctly.
select decrypt(encrypt('aéiou','foo','aes'),'foo','aes')
Could you tell me if there is an option for encoding or this function
only encrypt basic ascii?
They take bytea rather than text and return bytea too.
Does casting the result give you valid text?
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] does encrypt function support higher than basic ascii?
Sorry, as Richard said the issue was me not converting bytea to text. The blow
did it . thank you!
SELECT convert_from((select decrypt(encrypt((select convert('aéiou','LATIN1',
'LATIN1')),'foo','aes'),'foo','aes')),'UNICODE')
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Naoko Reeves
Sent: Tuesday, November 17, 2009 1:38 PM
To: Richard Huxton
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] does encrypt function support higher than basic ascii?
I have tried:
select decrypt(encrypt((select convert('aéiou','UTF8',
'LATIN1')),'foo','aes'),'foo','aes')
select decrypt(encrypt((select convert('aéiou','UNICODE',
'LATIN1')),'foo','aes'),'foo','aes')
select decrypt(encrypt((select convert('aéiou','LATIN1',
'LATIN1')),'foo','aes'),'foo','aes')
but none of above seems to be resolving the issue...
-Original Message-
From: Richard Huxton [mailto:d...@archonet.com]
Sent: Tuesday, November 17, 2009 1:14 PM
To: Naoko Reeves
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] does encrypt function support higher than basic ascii?
Naoko Reeves wrote:
Hello,
I have the following statement and accent e doesn't seems to be
decrypted correctly.
select decrypt(encrypt('aéiou','foo','aes'),'foo','aes')
Could you tell me if there is an option for encoding or this function
only encrypt basic ascii?
They take bytea rather than text and return bytea too.
Does casting the result give you valid text?
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] does encrypt function support higher than basic ascii?
I see that's how you cast...Yes that worked PERFECTLY. I am always learning
something new from the list. Thank you VERY much!
-Original Message-
From: Richard Huxton [mailto:d...@archonet.com]
Sent: Tuesday, November 17, 2009 2:07 PM
To: Naoko Reeves
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] does encrypt function support higher than basic ascii?
Naoko Reeves wrote:
Sorry, as Richard said the issue was me not converting bytea to text. The
blow did it . thank you!
SELECT convert_from((select decrypt(encrypt((select convert('aéiou','LATIN1',
'LATIN1')),'foo','aes'),'foo','aes')),'UNICODE')
I'm surprised you can't just do:
SELECT convert_from(
decrypt(
encrypt( 'aéiou'::bytea, 'foo', 'aes' )
, 'foo', 'aes'
)
, 'unicode'
)
You should be able to cast to bytea simply enough. Coming back the other
way, you do need to tell it what encoding you have through convert_from().
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Where do you store key for encryption
Hi, We have web application and encrypt PII columns. We use encrypt/decrypt function for this. Currently we hard coded the key in postgresql function which I am not sure of it. I did google it and people suggest that it needed to be stored in physically isolated location (storing decryption key on the same server as the data is kind of like writing your PIN on your ATM card). I would like to know how postgres professionals handles this. Thank you very much for your time in advance. Naoko
Re: [GENERAL] Where do you store key for encryption
Got it. Thank you very much for your advice. -Original Message- From: Merlin Moncure [mailto:mmonc...@gmail.com] Sent: Tuesday, November 17, 2009 8:54 PM To: Naoko Reeves Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Where do you store key for encryption On Tue, Nov 17, 2009 at 10:12 PM, Naoko Reeves na...@lawlogix.com wrote: Hi, We have web application and encrypt PII columns. We use encrypt/decrypt function for this. Currently we hard coded the key in postgresql function which I am not sure of it. I did google it and people suggest that it needed to be stored in physically isolated location (storing decryption key on the same server as the data is kind of like writing your PIN on your ATM card). Key management is a complicated topic, but I can tell you this for sure: storing the key in the function is one of the worst places to do it :-) Any user can pull down the entire pg_proc table and see all your functions! (this is somewhat fixable, but It's still not the right place IMO). merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Fast Search on Encrypted Feild
As Alban pointed out encrypting the search value and compare stored
encrypted value is very fast though it can't do LIKE search.
After I received valuable input from Merlin, Bill and John, I did some
research regarding search against encrypted field in general and as in
everyone's advice, I must acknowledge the cost of encrypted data for
searching and considering alternative method (partial encryption, store
in different table etc).
Thank you very again much for all the advice.
I have a encrypted column use encrypt function.
Querying against this column is almost not acceptable - returning 12
rows took 25,908 ms.
The query was simply Select decrypt(phn_phone_enc) FROM phn WHERE
decrypt(phn_phone_enc,'xxx','xxx') LIKE '123%'
So I built index like: CREATE INDEX idx_phn_phone_dec ON phn
(decrypt(phn_phone_enc, 'xxx', 'xxx'))
This returns 12 rows in 68 ms.
Would this be the solution for the fast encrypted field search or does
this raise the security issue?
Is there some way you can invert the process?
Normally if you verify encrypted data (typically passwords) you would
encrypt the user-specified data and compare that to the encrypted data
in the database instead of decrypting both and comparing the actual
data. I doubt you can do that with partial data though, and since you're
showing a LIKE expression here...
One thing I notice in your query is that you're decrypting your data
twice; you're calling two different functions for the same purpose. You
may notice a speed-up if you use decrypt(text) instead of decrypt(text,
text, text) in your where clause, provided that the function is defined
stable[1] or immutable[2] as opposed to the default volatile[3].
Or is decrypting only a part of the encrypted string significantly
faster? That would imply some linear encryption algorithm in which case
you may be able to use my initial suggestion: Encrypting '123' would
create something that's comparable to the first 3 encrypted characters
of an encrypted phone number. A query would look like: SELECT
decrypt(phn_phone_enc) FROM phn WHERE phn_phone_enc LIKE
encrypt('123')||'%'. Here as well it is important that encrypt(text) is
defined stable or immutable.
1. The same input data combined with data from the DB (as it is visible
to the transaction) always yields the same result.
2. The same input data always yields the same result.
3. There is no correlation between the input data and the result.
Alban Hertroys
--
Screwing up is the best way to attach something to the ceiling.
!DSPAM:991,4affebf711071508957761!
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Fast Search on Encrypted Feild
I have a encrypted column use encrypt function. Querying against this column is almost not acceptable - returning 12 rows took 25,908 ms. The query was simply Select decrypt(phn_phone_enc) FROM phn WHERE decrypt(phn_phone_enc,'xxx','xxx') LIKE '123%' So I built index like: CREATE INDEX idx_phn_phone_dec ON phn (decrypt(phn_phone_enc, 'xxx', 'xxx')) This returns 12 rows in 68 ms. Would this be the solution for the fast encrypted field search or does this raise the security issue? Kindest regards, Naoko Reeves
Re: [GENERAL] Fast Search on Encrypted Feild
Merlin, Thank you for your quick response. I see... our security requirements are: We are encrypting PII information within our DB and because of the sensitive nature of our data, we must balancing both performance and security to meet our client requirements. Our clients are mainly lawyers and handles clients case (government, healthcare, education). If you could provide me any advice that would be great otherwise I understand that I have to go without wildcard search. Naoko Would this be the solution for the fast encrypted field search or does this raise the security issue? You are storing the unencrypted phone number in the index...can't do that. As I see it, any solution that needs to support 'LIKE' (or anything other than equality case) is going to be problematic because it has to expose details of the encrypted data to work. It may be possible to rig something...how high are your security requirements? merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] how to Export ALL plpgsql functions/triggers to file
Hi, Could you tell me how to Export ALL plpgsql functions/triggers to file? Thank you
[GENERAL] Cascading of trigger execution level
Hello, Is there a way to identify cascading of trigger execution level? Please let me know if my question doesn't make any sense.
[GENERAL] Is there a way to know if trigger is invoked by the code from another trigger
Could you tell me if there is a way to know if trigger is invoked by the code from another trigger? For instance, table A Trigger deletes table B record. While in table B trigger, I want to know whether this was triggered from table A. Thank you, Naoko
[GENERAL] How to identify nested level of trigger
Is there a way to identify whether the given execution is in nested trigger or not?
Re: [GENERAL] Query and the number of row result
Which client do you use to access to db? GUI (pgAdmin?)? Command-line? Either way, it should give you row number... -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of bilal ghayyad Sent: Monday, August 31, 2009 4:11 AM To: David Fetter Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Query and the number of row result Just writing a Function in the PostgreSQL it self (so it is sql scripting). It is not from any development language. Regards Bilal --- On Sun, 8/30/09, David Fetter da...@fetter.org wrote: From: David Fetter da...@fetter.org Subject: Re: [GENERAL] Query and the number of row result To: bilal ghayyad bilmar...@yahoo.com Cc: pgsql-general@postgresql.org Date: Sunday, August 30, 2009, 10:41 PM On Sun, Aug 30, 2009 at 07:35:42PM -0700, bilal ghayyad wrote: Postgresql. Is there alot of Postgresql? How can I know mine? Are you connecting from C? PHP? Perl? Python? Ruby? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Aggregate function with Join stop working under certain condition
I am joining 4 tables (doc, bpt, con, emp) - emp and doc relationship is one to many. I want to only one doc record per emp as condition shown below: The query below successfully returns desired result (returns 4 records): Select bpt_key, emp_full_name, con_full_name, max(doc_date_created) as doc_date_created from bpt LEFT OUTER JOIN doc ON bpt_emp_key = doc_emp_key INNER JOIN emp on bpt_emp_key = emp_key LEFT OUTER JOIN con ON emp_con_key = con_key WHERE bpt_com_key = 22 and bpt_status-1 GROUP BY bpt_key, emp_full_name, con_full_name However, I wan to add one more doc column but as soon as I add one, it try to return all unique doc records. Could you tell me what am I doing wrong here please? As soon as I add one more column, it returns 6 records: Select bpt_key, emp_full_name, con_full_name, max(doc_date_created) as doc_date_created, doc_subject from bpt LEFT OUTER JOIN doc ON bpt_emp_key = doc_emp_key INNER JOIN emp on bpt_emp_key = emp_key LEFT OUTER JOIN con ON emp_con_key = con_key WHERE bpt_com_key = 22 and bpt_status-1 GROUP BY bpt_key, emp_full_name, con_full_name, doc_subject Kindest Regard, Naoko
Re: [GENERAL] Aggregate function with Join stop working under certain condition
The solution provide by Sam is unbelievably fast and works 100% accurately. Thank you very much. -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Sam Mason Sent: Wednesday, August 26, 2009 10:40 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Aggregate function with Join stop working under certain condition On Wed, Aug 26, 2009 at 11:17:10AM -0400, Naoko Reeves wrote: I am joining 4 tables (doc, bpt, con, emp) - emp and doc relationship is one to many. I want to only one doc record per emp as condition shown below: [...] However, I wan to add one more doc column but as soon as I add one, it try to return all unique doc records. Could you tell me what am I doing wrong here please? Descriptions of the problem are normally easier to understand than code; but I *guess* what you want to do is to get the subject of the last document created by each person and when it was created. If that's the case then DISTINCT ON is normally the easiest way. Maybe something like: SELECT b.bpt_key, e.emp_full_name, c.con_full_name, d.doc_date_created, d.doc_subject FROM bpt b, emp e LEFT JOIN con c ON e.emp_con_key = c.con_key LEFT JOIN ( SELECT DISTINCT ON (doc_emp_key) doc_emp_key, doc_date_created, doc_subject FROM doc ORDER BY doc_emp_key, doc_date_created DESC) d ON e.emp_key = d.doc_emp_key WHERE b.bpt_emp_key = e.emp_key AND b.bpt_com_key = 22 AND b.bpt_status -1; -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
