On 06/04/2014 10:49 PM, sramay wrote:
Sir,
The base table is having bytea and having records around 32 lakhs shows size
of 300 mb.
bytea field has attached documents size is not shown in the base table.
The message on Tomcat is
--begin text --
4 Jun, 2014 3:29:07 PM org.apache.catalina.core.
On Wednesday, June 04, 2014 10:49:18 PM sramay wrote:
> relation "public.file_attachments" does not exist
.. is almost certainly not a size problem. What does your PostgreSQL log say?
I suspect your app is connecting to the wrong database.
--
Sent via pgsql-general mailing list (pgsql-general@
Sir,
The base table is having bytea and having records around 32 lakhs shows size
of 300 mb.
bytea field has attached documents size is not shown in the base table.
The message on Tomcat is
--begin text --
4 Jun, 2014 3:29:07 PM org.apache.catalina.core.StandardWrapperValve invoke
INFO: WARN [
On 06/03/2014 10:02 PM, sramay wrote:
Sir,
No it is reporting that base table is missing not the TOAST table.
Is Streaming Replication can cause this issue?.
What is the exact error message Tomcat is reporting?
Data is not being
aged which is much. As the records add during this period on
Sir,
No it is reporting that base table is missing not the TOAST table.
Is Streaming Replication can cause this issue?. Data is not being
aged which is much. As the records add during this period on the base
table
which is hardly 300 MB ( 3million records) . the db size 700 GB.
Version I am
On 06/03/2014 01:25 AM, sramay wrote:
Hi all,
I am having a specifc issue of bytea. When we started storing more records
in the database on bytea field on version 9.1.x the data has gone to
pg_toast tables. The strange observation is now after a table size of 700
gb it now all of a sudden re
Hi all,
I am having a specifc issue of bytea. When we started storing more records
in the database on bytea field on version 9.1.x the data has gone to
pg_toast tables. The strange observation is now after a table size of 700
gb it now all of a sudden reporting table not found on the tomcat lo
Merlin Moncure-2 wrote
>
> m_connection.prepare("INSERT INTO foo(x) VALUES($1)") ("bytea",
> pqxx::prepare::treat_binary);
>
I have the same problem. I can't get accsess to pqxx::prepare::treat_binary.
And m_connection.prepare(...) two arguments only. What i mist?
--
View this message in cont
2011/10/2 Merlin Moncure
> On Sat, Oct 1, 2011 at 4:27 AM, Dmitriy Igrishin
> wrote:
> > Hey Merlin,
> >
> >> The lo interface sucks but it's slightly better on resources for
> >> really huge bytea and tends to be more consistently implemented in
> >> database drivers. If I was doing this, I wo
On Sat, Oct 1, 2011 at 4:27 AM, Dmitriy Igrishin wrote:
> Hey Merlin,
>
>> The lo interface sucks but it's slightly better on resources for
>> really huge bytea and tends to be more consistently implemented in
>> database drivers. If I was doing this, I would of course be crafting
>> a carefully
Hey Merlin,
The lo interface sucks but it's slightly better on resources for
> really huge bytea and tends to be more consistently implemented in
> database drivers. If I was doing this, I would of course be crafting
> a carefully generated client in C, using libpqtypes, which is the gold
> stand
On Thu, Sep 29, 2011 at 10:54 AM, Jon Nelson wrote:
> On Thu, Sep 29, 2011 at 10:51 AM, David North wrote:
>
>> I'll also apply GZip to keep the amount of data in any given bytea/lob as
>> small as possible.
>
> Aren't bytea fields compressed by postgresql anyway (when EXTENDED or
> MAIN is used)
On Thu, Sep 29, 2011 at 10:51 AM, David North wrote:
> I'll also apply GZip to keep the amount of data in any given bytea/lob as
> small as possible.
Aren't bytea fields compressed by postgresql anyway (when EXTENDED or
MAIN is used) (by default).
http://www.postgresql.org/docs/8.4/static/stora
On 29/09/11 14:55, Merlin Moncure wrote:
On Thu, Sep 29, 2011 at 6:12 AM, Radosław Smogura
wrote:
On Wed, 28 Sep 2011 10:18:27 -0500, Merlin Moncure wrote:
sending ~1GB bytea values is borderline crazy, and is completely crazy
if you are not absolutely sure the transmission is not 100% binary
On Thu, Sep 29, 2011 at 15:48, Jon Nelson wrote:
> especially if my attempt
> to do so kills the backend I am using (which triggers a shutdown of
> all other backends, no?).
No, this is just an ereport(ERROR) that's handled gracefully by
rolling back the transaction.
Regards,
Marti
--
Sent via
On Thu, Sep 29, 2011 at 6:12 AM, Radosław Smogura
wrote:
> On Wed, 28 Sep 2011 10:18:27 -0500, Merlin Moncure wrote:
>> sending ~1GB bytea values is borderline crazy, and is completely crazy
>> if you are not absolutely sure the transmission is not 100% binary. I
>> don't know if the JDBC sends/r
On Thu, Sep 29, 2011 at 7:30 AM, Alban Hertroys wrote:
> On 29 September 2011 13:12, Radosław Smogura wrote:
>>> sending ~1GB bytea values is borderline crazy, and is completely crazy
>>> if you are not absolutely sure the transmission is not 100% binary. I
>>> don't know if the JDBC sends/recei
On 29 September 2011 13:12, Radosław Smogura wrote:
>> sending ~1GB bytea values is borderline crazy, and is completely crazy
>> if you are not absolutely sure the transmission is not 100% binary. I
>> don't know if the JDBC sends/receives bytea as binary, but it may not.
>> If not, you might ha
On Wed, 28 Sep 2011 10:18:27 -0500, Merlin Moncure wrote:
On Wed, Sep 28, 2011 at 3:28 AM, David North
wrote:
On 28/09/11 01:50, Craig Ringer wrote:
On 09/28/2011 01:01 AM, David North wrote:
testdb=# select * from problem_table;
ERROR: invalid memory alloc request size 2003676411
What Pg
On Tue, Sep 27, 2011 at 20:01, David North wrote:
> testdb=# select * from problem_table;
> ERROR: invalid memory alloc request size 2003676411
> Is there some reason why my data can be stored in <1GB but triggers the
> allocation of 2GB of memory when I try to read it back? Is there any setting
On Wed, Sep 28, 2011 at 3:28 AM, David North wrote:
> On 28/09/11 01:50, Craig Ringer wrote:
>>
>> On 09/28/2011 01:01 AM, David North wrote:
>>
>>> testdb=# select * from problem_table;
>>> ERROR: invalid memory alloc request size 2003676411
>>
>> What Pg version are you using?
>>
>> On which pla
On 28/09/11 01:50, Craig Ringer wrote:
On 09/28/2011 01:01 AM, David North wrote:
testdb=# select * from problem_table;
ERROR: invalid memory alloc request size 2003676411
What Pg version are you using?
On which platform?
32-bit or 64-bit OS? If 64-bit, with a 32-bit or 64-bit build of
Pos
On 09/28/2011 01:01 AM, David North wrote:
testdb=# select * from problem_table;
ERROR: invalid memory alloc request size 2003676411
What Pg version are you using?
On which platform?
32-bit or 64-bit OS? If 64-bit, with a 32-bit or 64-bit build of PostgreSQL?
--
Craig Ringer
--
Sent via pg
My application uses a bytea column to store some fairly large binary
values (hundreds of megabytes).
Recently I've run into a problem as my values start to approach the 1GB
limit on field size:
When I write a 955MB byte array from Java into my table from JDBC, the
write succeeds and the numb
On Mon, Sep 26, 2011 at 8:50 AM, Gregg Jaskiewicz wrote:
> Thanks Merin.
>
> It does, and that's probably what I'll do. Your solution isn't great
> either, because it requires extra function to be run on the postgresql
> side. Me no likeey that ;)
If you are sending a bytea as encoded text, you h
On Mon, Sep 26, 2011 at 5:51 AM, Gregg Jaskiewicz wrote:
> So consider this code C++, using libpqxx:
>
> string = "INSERT INTO foo(x) VALUES( E'" + T.esc_raw(data) + "' )";
>
> foo(x) is bytea , before you ask.
>
> On 8.3, it works fine.
> On 9.x:
>
> ERROR: invalid byte sequence for encoding "UT
On 26 September 2011 14:39, Merlin Moncure wrote:
> urk -- I have to be honest -- that's a pretty lousy way to send bytea.
> Personally, I'd encode the string as hex and send it like this:
>
> "INSERT INTO foo(x) VALUES( decode('" + hex_string + "'))";
>
> libpqxx doesn't have the ability to para
So consider this code C++, using libpqxx:
string = "INSERT INTO foo(x) VALUES( E'" + T.esc_raw(data) + "' )";
foo(x) is bytea , before you ask.
On 8.3, it works fine.
On 9.x:
ERROR: invalid byte sequence for encoding "UTF8": 0x00 (if \000 is in
the string).
Now, I can take out the E'' and it
On Tue, Dec 14, 2010 at 11:22 AM, tuanhoanganh wrote:
> Thanks for your answer. But I don't want to change db config or ALTER
> DATABASE data SET bytea_output='escape';
> I can change my app. So is there document introduce how to work with
> postgresql 9 bytea
Your .Net or whatever library you're
Latest Npgsql version 2.0.11 already has a fix to handle new bytea
representation.
Can you try with this version and see if it works ok?
I hope it helps.
On Tue, Dec 14, 2010 at 14:22, tuanhoanganh wrote:
> Thanks for your answer. But I don't want to change db config or ALTER
> DATABASE data SE
tuanhoanganh writes:
> Thanks for your answer. But I don't want to change db config or ALTER
> DATABASE data SET bytea_output='escape';
> I can change my app. So is there document introduce how to work with
> postgresql 9 bytea ( I don't want to user lo_create, lo_import. lo_export)
http://www.po
Thanks for your answer. But I don't want to change db config or ALTER
DATABASE data SET bytea_output='escape';
I can change my app. So is there document introduce how to work with
postgresql 9 bytea ( I don't want to user lo_create, lo_import. lo_export)
Tuan Hoang Anh
On Tue, Dec 14, 2010 at 8:5
On 14/12/2010 14:51, tuanhoanganh wrote:
Thanks for your help.
Is there any .Net or VB tutorial new 9.0 bytea?
You do not need to change your code if you add
bytea_output = 'escape' # hex, escape
into postgresql.conf.
--
Sent via pgsql-general mailing list (pgsql-general@po
Thanks for your help.
Is there any .Net or VB tutorial new 9.0 bytea?
Tuan Hoang Anh
On Tue, Dec 14, 2010 at 1:27 PM, Mark Felder wrote:
> On Mon, 13 Dec 2010 23:06:32 -0600, tuanhoanganh
> wrote:
>
> I have program work with bytea, this field store image. Program work well
>> in
>> postgresq
On Mon, 13 Dec 2010 23:06:32 -0600, tuanhoanganh
wrote:
I have program work with bytea, this field store image. Program work
well in
postgresql 8.3.9 but error in postgresql 9.0
I don't know if this is your problem, but bytea changed in Postgres 9.0.
Could you try enabling "set bytea_ou
I have program work with bytea, this field store image. Program work well in
postgresql 8.3.9 but error in postgresql 9.0
Here is code to write image to database
FileStream srcStream = new FileStream(file_name, FileMode.Open,
FileAccess.Read);
byte[] arrImage = new byte[srcStream.Length];
int read
I have program work with bytea, this field store image. Program work well in
postgresql 8.3.9 but error in postgresql 9.0
Here is code to write image to database
FileStream srcStream = new FileStream(file_name, FileMode.Open,
FileAccess.Read);
byte[] arrImage = new byte[srcStream.Length];
int read
Hi!
PG8,4, Window XP, Python.
I have a program that makes many picture version from an original with
XNView effects.
Because I want preserve the integrity, previous datas, I used transactions,
and PGSQL.
The main problem with blobs that insertion is good, the select is good, but
the deletion is
On Mon, 28 Sep 2009, Maximilian Tyrtania wrote:
> testdb=# create table byteatest(blob bytea);
> CREATE TABLE
> testdb=# insert into byteatest (blob) values (E'\\007');
> INSERT 0 1
> testdb=# insert into byteatest (blob) values (E'\\008');
> ERROR: invalid input syntax for type bytea
> LINE 1: i
PG 8.4.0 running on Mac OS 10.6.1
Could anyone tell me why the bytea datatypes seems to like some bytes better
than others?
testdb=# create table byteatest(blob bytea);
CREATE TABLE
testdb=# insert into byteatest (blob) values (E'\\007');
INSERT 0 1
testdb=# insert into byteatest (blob) values (E
thank you very much, all. i was able to insert my data and get it back
out with a matching hash. my problems were caused by confusion going
between plperlu - which has the bytea storage explicit custom encoding
requirement - and regular perl using dbd::pg - which does not as long
as the data type i
I'm probably a little late to this discussion, but I have had issues before
with BYTEA in postgres before as well, this is what I found worked.
use Digest::MD5;
use DBI qw(:sql_types);
use DBD::Pg qw(:pg_types);
sub InsertBin($$$)
{
my ( $dbh, $md5sum, $filename ) = @_;
Nathan Jahnke wrote:
> thanks for your help. unfortunately i'm still getting corruption on
> this particular data (available at
> http://nate.quandra.org/data.bin.0.702601051229191 ) even with these
> changes:
>
> # ./bytea.pl
> Argument "DBD::Pg::PG_BYTEA" isn't numeric in subroutine ent
wrong reply-address; please disregard the last message from me.
thanks for your help. unfortunately i'm still getting corruption on
this particular data (available at
http://nate.quandra.org/data.bin.0.702601051229191 ) even with these
changes:
# ./bytea.pl
Argument "DBD::Pg::PG_BYTEA" isn't num
Nathan Jahnke wrote:
> good catch - it's because i'm used to working in plperlu.
> unfortunately commenting out those lines makes no difference for this
> particular data (that i linked in my original email); it's still
> corrupted:
Don't remove both: remove only the custom decoding.
It'
good catch - it's because i'm used to working in plperlu.
unfortunately commenting out those lines makes no difference for this
particular data (that i linked in my original email); it's still
corrupted:
# ./bytea.pl
37652cf91fb8d5e41d3a90ea3a22ea61 != ce3fc63b88993af73fb360c70b7ec965
nathan
O
Nathan Jahnke wrote:
> [...]
> my $encodeddata = $data;
> $encodeddata =~ s!(\\|[^ -~])!sprintf("\\%03o",ord($1))!ge; #prepare
> data for bytea column storage
> [...]
> my $insert_sth = $connection->prepare('insert into testtable (data)
> values (?) returning id');
> $insert_sth->execute($encod
got some binary data that changes when i insert and retrieve it later
from bytea column:
http://nate.quandra.org/data.bin.0.702601051229191
running 8.3 on debian 5.0.
example:
root=# create database testdb;
CREATE DATABASE
root=# \c testdb
You are now connected to database "testdb".
testdb=# cr
Please don't top post.
paulo matadr wrote:
> I think identified the problem
> lts's check log below:
>
> Query:
> SELECTSTATEMENT: select relatorios0_.fuin_id as fuin5_1_,
> relatorios0_.rege_id as rege1_1_, relatorios0_.rege_id as r
> ege1_624_0_, relatorios0_.rege_tmultimaalteracao as rege2_6
checkpoints don't have anything to do with it.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
e: Albe Laurenz
Para: paulo matadr *EXTERN*
Cc: GENERAL ; admin
Enviadas: Quinta-feira, 22 de Janeiro de 2009 6:26:26
Assunto: Re: [ADMIN] [GENERAL] bytea size limit?
Please don't top post.
paulo matadr wrote:
>>> My aplication return erro:
>>>
>>> 2
Please don't top post.
paulo matadr wrote:
>>> My aplication return erro:
>>>
>>> 2009-01-20 10:22:03,264 INFO [STDOUT] Caused by: org.postgresql.util.
>>> PSQLException: ERROR: invalid memory alloc request size 1705447581
>>
>> What are you doing in terms of SQL?
>> INSERT, UPDATE, DELETE?
>>
,if this bigger , hangs .
No able to select or others report in this table.
De: Albe Laurenz
Para: paulo matadr *EXTERN* ;
pgsql-general@postgresql.org; admin
Enviadas: Quarta-feira, 21 de Janeiro de 2009 14:03:17
Assunto: Re: [ADMIN] [GENERAL] bytea
paulo matadr wrote:
> I everyone, need help!!!
> My aplication return erro:
>
> 2009-01-20 10:22:03,264 INFO [STDOUT] Caused by: org.postgresql.util.
> PSQLException: ERROR: invalid memory alloc request size 1705447581
>
>
> TABLE batch.relatorio_gerado
>
> rege_id integer NOT NULL,
> fu
On 1/21/09, paulo matadr wrote:
>
> I everyone, need help!!!
> My aplication return erro:
>
> 2009-01-20 10:22:03,264 INFO [STDOUT] Caused by: org.postgresql.util.
> PSQLException: ERROR: invalid memory alloc request size 1705447581
What exactly were you doing when you got the error?
How big is
On Wed, Jan 21, 2009 at 03:07:13PM +, Grzegorz Ja??kiewicz wrote:
> On Wed, Jan 21, 2009 at 3:02 PM, paulo matadr wrote:
> > My system have very large ram size, so its possible review postgresql.conf ?
> >
> all depends on how you access DB, what type of drivers (odbc, libpq, etc,
> etc).
> S
On Wed, Jan 21, 2009 at 3:02 PM, paulo matadr wrote:
> My system have very large ram size, so its possible review postgresql.conf ?
>
all depends on how you access DB, what type of drivers (odbc, libpq, etc, etc).
See, every time you pass a row , they usually have to allocate that
much memory, not
] [GENERAL] bytea size limit?
On Wed, Jan 21, 2009 at 01:55:28PM +, Grzegorz Ja??kiewicz wrote:
> there's no real limit (its size is described with 32bit number, and
> that's the only limitation here).
> But you need to be aware, that content is sent over at once, so memory
>
you don't have to quote everything :)
I ment, there's nothing on bytea on its doc page, where one would
expect to read it.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Wed, Jan 21, 2009 at 02:09:01PM +, Grzegorz Ja??kiewicz wrote:
> On Wed, Jan 21, 2009 at 2:06 PM, Kenneth Marshall wrote:
>
> > The TOAST implementation however only allows 30-bits for the
> > size of the TOAST entry which caps the size at 2^30 or 1GB. I
> > agree that he could very well b
On Wed, Jan 21, 2009 at 2:06 PM, Kenneth Marshall wrote:
> The TOAST implementation however only allows 30-bits for the
> size of the TOAST entry which caps the size at 2^30 or 1GB. I
> agree that he could very well be limited also by the memory on
> his system.
i wasn't aware of that, and also
On Wed, Jan 21, 2009 at 01:55:28PM +, Grzegorz Ja??kiewicz wrote:
> there's no real limit (its size is described with 32bit number, and
> that's the only limitation here).
> But you need to be aware, that content is sent over at once, so memory
> is the limit in your case.
>
> http://www.postg
there's no real limit (its size is described with 32bit number, and
that's the only limitation here).
But you need to be aware, that content is sent over at once, so memory
is the limit in your case.
http://www.postgresql.org/docs/8.3/static/datatype-binary.html
For such large objects, it might b
I everyone, need help!!!
My aplication return erro:
2009-01-20 10:22:03,264 INFO [STDOUT] Caused by: org.postgresql.util.
PSQLException: ERROR: invalid memory alloc request size 1705447581
TABLE batch.relatorio_gerado
rege_id integer NOT NULL,
fuin_id integer NOT NULL,
rela_id integer NO
> ISTM that in this line:
>
> keying = (text *)palloc( keylen + unamelen );
>
> You forgot to include the length of the header VARHDRSZ.
>
Aha, that'd be it, it's been a long day.
Thanks Martijn
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your
On Thu, Oct 30, 2008 at 05:27:58PM +, Glyn Astill wrote:
> Hi chaps,
>
> I think I'm going to struggle to describe this, but hopefully someone can
> squint and see where I'm going wrong.
>
> I've got a c function called "ftest", all it does is take some text and
> prepend "abcdefghijklmnopq
Hi chaps,
I think I'm going to struggle to describe this, but hopefully someone can
squint and see where I'm going wrong.
I've got a c function called "ftest", all it does is take some text and prepend
"abcdefghijklmnopqr" onto it. I use it to pass a key into
pgp_sym_encrypt/decrypt working on
Steve Atkins wrote:
So, yeah, you're right. Generally, email is too complex to deal with
in the database as anything other than an opaque bytea blob, along
with some metadata
Only because that's the choice made by dbmail. As an IMAP server, it
doesn't _have_ to do more. The downs
On Aug 7, 2008, at 5:28 PM, Klint Gore wrote:
Alvaro Herrera wrote:
Merlin Moncure escribió:
> er, I see the problem (single piece of text with multiple encodings
> inside) :-). ok, it's more complicated than I thought. still, you
> need to convert the email to utf8. There simply must be a
Alvaro Herrera wrote:
Merlin Moncure escribió:
> er, I see the problem (single piece of text with multiple encodings
> inside) :-). ok, it's more complicated than I thought. still, you
> need to convert the email to utf8. There simply must be a way,
> otherwise your emails are not well define
Merlin,
You are suggesting a fight with the flexible dynamics of email by
fitting it into a UTF shell - it doesn't always work.
I would suggest you read the postgresql definition of SQL-ASCII:
> The SQL_ASCII setting behaves considerably differently from the other
> settings. When the server cha
Merlin Moncure escribió:
> er, I see the problem (single piece of text with multiple encodings
> inside) :-). ok, it's more complicated than I thought. still, you
> need to convert the email to utf8. There simply must be a way,
> otherwise your emails are not well defined. This is a client sid
On Thu, Aug 7, 2008 at 9:38 AM, Merlin Moncure <[EMAIL PROTECTED]> wrote:
> On Thu, Aug 7, 2008 at 1:16 AM, Sim Zacks <[EMAIL PROTECTED]> wrote:
>>
>>> I don't quite follow that...the whole point of utf8 encoded database
>>> is so that you can use text functions and operators without the bytea
>>>
On Thu, Aug 7, 2008 at 1:16 AM, Sim Zacks <[EMAIL PROTECTED]> wrote:
>
>> I don't quite follow that...the whole point of utf8 encoded database
>> is so that you can use text functions and operators without the bytea
>> treatment. As long as your client encoding is set up properly (so
>> that data
I ran the update, but now (obviously) it wants to vacuum again and
vacuum on that table took 9 hours yesterday.
Do the statistics change when changing the storage type? Meaning does it
really need to vacuum?
Thank you
Sim
Tom Lane wrote:
> Sim Zacks <[EMAIL PROTECTED]> writes:
>> After the alter
> I don't quite follow that...the whole point of utf8 encoded database
> is so that you can use text functions and operators without the bytea
> treatment. As long as your client encoding is set up properly (so
> that data coming in and out is computed to utf8), then you should be
> ok. Dropping
On Wed, Aug 6, 2008 at 9:16 AM, Sim Zacks <[EMAIL PROTECTED]> wrote:
> We are using UTF-8, and I am testing SQL-ASCII at the moment. DBMail is
> a pre-built application, so until I am ready to start playing with its
> internals I don't really have a choice about a number of its features.
> The reas
Sim Zacks wrote:
DBMail is
a pre-built application, so until I am ready to start playing with its
internals I don't really have a choice about a number of its features.
Have you heard of this? Might be worth a quick look:
http://www.archiveopteryx.org/overview
--
Richard Huxton
Archonet L
Sim Zacks <[EMAIL PROTECTED]> writes:
> After the alter table, then I have to update each row with an
> update dbmail_messageblks set messageblk=messageblk;
> so that it uses the new storage.
I wouldn't actually bet on that changing anything at all ...
I'd try something like messageblk = messagebl
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Tom Lane wrote:
> We've seen complaints about toast fetch time before. I don't think
> there's any really simple solution. You could experiment with disabling
> compression (SET STORAGE external) but I'd bet on that being a net loss
> unless the dat
Sim Zacks <[EMAIL PROTECTED]> writes:
> Results below:
>> ... but given that, I wonder whether the cost isn't from fetching
>> the toasted messageblk data, and nothing directly to do with either
>> the encode() call or the ~~ test. It would be interesting to compare
>> the results of
Okay, so sub
Results below:
> ... but given that, I wonder whether the cost isn't from fetching
> the toasted messageblk data, and nothing directly to do with either
> the encode() call or the ~~ test. It would be interesting to compare
> the results of
>
> explain analyze select encode(messageblk, 'escape')
Sim Zacks <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> Could we see EXPLAIN ANALYZE, not EXPLAIN? Without actual facts
>> to work from, any suggestions would be mere guesswork.
> " -> Seq Scan on dbmail_messageblks k
> (cost=0.00..39193.21 rows=259 width=764) (actual time=30.662
On 2008-08-03 12:12, Sim Zacks wrote:
> SELECT m.message_idnr,k.messageblk
> FROM dbmail_messageblks k
> JOIN dbmail_physmessage p ON k.physmessage_id = p.id
> JOIN dbmail_messages m ON p.id = m.physmessage_id
> WHERE
> mailbox_idnr = 8
> AND status IN (0,1 )
> AND k.is_header = '0'
> GROUP BY
Tom Lane wrote:
> Could we see EXPLAIN ANALYZE, not EXPLAIN? Without actual facts
> to work from, any suggestions would be mere guesswork.
This was taken immediately after a vacuum analyze on the database.
"HashAggregate (cost=41596.68..41596.84 rows=16 width=764) (actual
time=488263.802..48826
Sim Zacks <[EMAIL PROTECTED]> writes:
> The explain of the query is:
Could we see EXPLAIN ANALYZE, not EXPLAIN? Without actual facts
to work from, any suggestions would be mere guesswork.
Also, what can you tell us about the sizes of the messageblk
strings (max and avg would be interesting)?
The LIKE operator is likely the problem, but it is a critical part of an
email application. Searches are done by, "Show me all emails containing
the following word."
I've tried using TSearch2's full text index. It made the query 50%
faster, taking 5 minutes. This is still not even close to the les
On Aug 3, 2008, at 2:36 PM, Sim Zacks wrote:
The LIKE operator is likely the problem, but it is a critical part
of an
email application. Searches are done by, "Show me all emails
containing
the following word."
I've tried using TSearch2's full text index. It made the query 50%
faster, takin
On Aug 3, 2008, at 12:12 PM, Sim Zacks wrote:
This is the query that is used (I know it is not as efficient as it
could be, but this is the query it comes with):
SELECT m.message_idnr,k.messageblk FROM dbmail_messageblks k JOIN
dbmail_physmessage p ON k.physmessage_id = p.id JOIN dbmail_message
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
I am using postgresql 8.2.7 on gentoo for my dbmail backend.
I am also testing it on mysql 5.
I am trying to figure out if I need to tune my database configuration or
if querying a bytea field is just not practical in postgresql.
Searching with the m
On May 21, 2008, at 8:23 AM, Sim Zacks wrote:
We are testing dbmail on postgresql, which uses a bytea column to
store
the messages. Because of this searching through messages is case
sensitive.
Does anyone know of a way to make it not case-sensitive, either by
changing the data type (obvio
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
We are testing dbmail on postgresql, which uses a bytea column to store
the messages. Because of this searching through messages is case sensitive.
Does anyone know of a way to make it not case-sensitive, either by
changing the data type (obviously wi
On 05/05/2008 16:07, Lee Feigenbaum wrote:
INSERT INTO myTable VALUES (..., E'\x15\x1C\x2F\x00\x02...', ...) ;
As I understand it, the octets need to be entered as their octal
representation - have a look at table 8-7 at
http://www.postgresql.org/docs/8.3/static/datatype-binary.html.
HTH,
Lee Feigenbaum <[EMAIL PROTECTED]> writes:
> Would be nice if the bytea parser understood hex representation too, but
> beggars can't be choosers :)
decode() might help you:
select decode('1200AB', 'hex');
decode
--
\022\000\253
(1 row)
regards, tom
Asche wrote:
Hi Lee,
Thanks for the suggestion. I should have mentioned in my original
message that as per your suggestion and the suggestion in the
documentation, I have tried escaping the backslashes. When I do this,
I get the error:
ERROR: invalid input syntax for type bytea
I tried al
I'm thinking that the answer is in the literal interpretation of the
error message, i.e. it doesn't like the specific byte 0x00, i.e. the
null byte. According to the docs (4.1.2.1. String Constants):
"The character with the code zero cannot be in a string constant."
The reason may be that th
Hi Lee,
Thanks for the suggestion. I should have mentioned in my original
message that as per your suggestion and the suggestion in the
documentation, I have tried escaping the backslashes. When I do
this, I get the error:
ERROR: invalid input syntax for type bytea
I tried also doing
Asche wrote:
Hi Lee,
On 05.05.2008, at 17:07, Lee Feigenbaum wrote:
INSERT INTO myTable VALUES (..., E'\x15\x1C\x2F\x00\x02...', ...) ;
try escaping the backslashes:
INSERT INTO myTable VALUES (..., E'\\x15\\x1C\\x2F\\x00\\x02...', ...) ;
Hi Jan,
Thanks for the suggestion. I should have m
Hi Lee,
On 05.05.2008, at 17:07, Lee Feigenbaum wrote:
INSERT INTO myTable VALUES (..., E'\x15\x1C\x2F\x00\x02...', ...) ;
try escaping the backslashes:
INSERT INTO myTable VALUES (..., E'\\x15\\x1C\\x2F\\x00\\x02...', ...) ;
Jan
--
Sent via pgsql-general mailing list (pgsql-general@postgre
Hi,
I've searched the archives a fair amount on this topic, but have not
found quite the answer / explanation I'm looking for. I attribute this
to my eternal confusion over character encoding issues in all
environments, so I apologize in advance for what might be a stupid
question. :)
I'mm
1 - 100 of 165 matches
Mail list logo
