Re: [GENERAL] Why PGSQL has no developments in the .NET area?

[Rob Sargent]

Rohit Coder wrote:
PgSQL has just one old NPGSQL driver for .NET, which is itself 
sluggish. The ODBC driver works better as compared to NPGSQL, but I 
suspect the ODBC driver is not the right choice for ORM framework of .NET.


I want to know whether there is any efficient .NET provider and is 
PGSQL compatible with the .NET entity framework.


Regards,
Rohit.
Won't bite on the "why" of it all, but is this 
 what you're after?



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

[GENERAL] Why PGSQL has no developments in the .NET area?

[Rohit Coder]

PgSQL has just one old NPGSQL driver for .NET, which is itself sluggish. The 
ODBC driver works better as compared to NPGSQL, but I suspect the ODBC driver 
is not the right choice for ORM framework of .NET.

I want to know whether there is any efficient .NET provider and is PGSQL 
compatible with the .NET entity framework.

Regards,
Rohit.
  

Re: [GENERAL] SQL Help - Finding Next Lowest Value of Current Row Value

[Chris Curvey]

On Sat, Oct 1, 2011 at 5:30 PM, Jeff Adams  wrote:

> Greetings,
>
> I have a large table (~19 million records). Records contains a field
> identifying a vessel and a field containing an time (epoch). Using the
> current rows vessel and time values, I need to be able to find the next
> lowest time value for the vessel and use it to compute how much time has
> elapsed between the records. I have tried a scalar subquery in the SELECT,
> which works, but it runs quite slowly. Is there an alternative approach
> that
> might perform better for this type of query. Any information would be
> greatly appreciated. Thanks...
>
> Jeff
>
>
>
Would a self-join with a MAX() help, like this?  (Where "v" is your
vessel_id and "e" is your time value?)

create table stuff
(  v int
,  e timestamp
);

insert into stuff (v, e) values (1, '1/1/2011');
insert into stuff (v, e) values (1, '1/2/2011');
insert into stuff (v, e) values (1, '1/3/2011');

insert into stuff (v, e) values (2, '2/1/2011');
insert into stuff (v, e) values (2, '2/2/2011');

select a.v, a.e, max(b.e), a.e - max(b.e)
from stuff a
join stuff b on a.v = b.v
where a.e > b.e
group by a.v, a.e

I don't have a multi-million row table handy, but I'd be interested to hear
your results.

-- 
e-Mail is the equivalent of a postcard written in pencil.  This message may
not have been sent by me, or intended for you.  It may have been read or
even modified while in transit.  e-Mail disclaimers have the same force in
law as a note passed in study hall.  If your corporate attorney says that
you need an disclaimer in your signature, you need a new corporate attorney.

Re: [GENERAL] Change server encoding after the fact

[Cody Caughlan]

Thanks y'all for your help on this.

I took this opportunity to upgrade to 9.1.1 which is UTF8 by default and I
ended up manually cleaning up the borked data by hand (there wasn't that
much).

So all is well now.

Thanks again.

/Cody



On Fri, Sep 30, 2011 at 3:37 PM, Scott Marlowe wrote:

> On Fri, Sep 30, 2011 at 2:57 PM, Cody Caughlan  wrote:
> > Please see below.
> >
> > On Fri, Sep 30, 2011 at 1:12 PM, Scott Marlowe 
> > wrote:
> >>
> >> On Fri, Sep 30, 2011 at 1:45 PM, Cody Caughlan 
> wrote:
> >> > That worked, but "file" shows no difference:
> >> > $ iconv -f utf-8 -t utf-8 -c foo.sql > utf.sql
> >> > $ file -i foo.sql
> >> > foo.sql: text/plain; charset=us-ascii
> >> > $file -i utf.sql
> >> > utf.sql: text/plain; charset=us-ascii
> >> > So iconv didnt actually convert the file OR does is the "file" command
> >> > just
> >> > ignorant?
> >>
> >> Not sure.  try loading the dump into the UTF-8 DB in postgres and see
> >> what happens I guess?
> >
> >
> > Uh oh.
> > On the remote machine:
> > $ pg_dump -Fc -E UTF8 foo > foo.sql
> > Then I've created a new local DB with UTF8 encoding and I try to restore
> > this dump into it:
> > pg_restore: [archiver (db)] Error while PROCESSING TOC:
> > pg_restore: [archiver (db)] Error from TOC entry 2342; 0 17086 TABLE DATA
> > wine_books vinosmith
> > pg_restore: [archiver (db)] COPY failed for table "wine_books": ERROR:
> >  invalid byte sequence for encoding "UTF8": 0xc309
> > CONTEXT:  COPY wine_books, line 1147
> > WARNING: errors ignored on restore: 1
> > And sure enough the table "wine_books" is empty. Not good.
>
> You may have to hunt down that one bad line (1147) and chop it out /
> edit it so it works.
>

Re: [GENERAL] Searching for "bare" letters

[Cody Caughlan]

One approach would be to "normalize" all the text and search against that.

That is, basically convert all non-ASCII characters to their equivalents. 

I've had to do this in Solr for searching for the exact reasons you've 
outlined: treat "ñ" as "n". Ditto for "ü" -> "u", "é" => "e", etc.

This is easily done in Solr via the included ASCIIFoldingFilterFactory:

http://wiki.apache.org/solr/AnalyzersTokenizersTokenFilters#solr.ASCIIFoldingFilterFactory

You could look at the code to see how they do the conversion and implement it.

/Cody

On Oct 1, 2011, at 7:09 PM, planas wrote:

> On Sun, 2011-10-02 at 01:25 +0200, Reuven M. Lerner wrote:
>> Hi, everyone.  I'm working on a project on PostgreSQL 9.0 (soon to be 
>> upgraded to 9.1, given that we haven't yet launched).  The project will 
>> involve numerous text fields containing English, Spanish, and Portuguese.  
>> Some of those text fields will be searchable by the user.  That's easy 
>> enough to do; for our purposes, I was planning to use some combination of 
>> LIKE searches; the database is small enough that this doesn't take very much 
>> time, and we don't expect the number of searchable records (or columns 
>> within those records) to be all that large.
>> 
>> The thing is, the people running the site want searches to work on what I'm 
>> calling (for lack of a better term) "bare" letters.  That is, if the user 
>> searches for "n", then the search should also match Spanish words containing 
>> "ñ".  I'm told by Spanish-speaking members of the team that this is how they 
>> would expect searches to work.  However, when I just did a quick test using 
>> a UTF-8 encoded 9.0 database, I found that PostgreSQL didn't  see the two 
>> characters as identical.  (I must say, this is the behavior that I would 
>> have expected, had the Spanish-speaking team member not said anything on the 
>> subject.)
>> 
>> So my question is whether I can somehow wrangle PostgreSQL into thinking 
>> that "n" and "ñ" are the same character for search purposes, or if I need to 
>> do something else -- use regexps, keep a "naked," searchable version of each 
>> column alongside the native one, or something else entirely -- to get this 
>> to work.
>> 
> Could you parse the search string for the non-English characters and convert 
> them to the appropriate English character? My skills are not that good or I 
> would offer more details.
>> Any ideas?
>> 
>> Thanks,
>> 
>> Reuven
>> 
>> 
>> -- 
>> Reuven M. Lerner -- Web development, consulting, and training
>> Mobile: +972-54-496-8405 * US phone: 847-230-9795
>> Skype/AIM: reuvenlerner
> 
> 
> -- 
> Jay Lozier
> jsloz...@gmail.com

Re: [GENERAL] Searching for "bare" letters

[Uwe Schroeder]

> Hi, everyone.  I'm working on a project on PostgreSQL 9.0 (soon to be
> upgraded to 9.1, given that we haven't yet launched).  The project will
> involve numerous text fields containing English, Spanish, and Portuguese. 
> Some of those text fields will be searchable by the user.  That's easy
> enough to do; for our purposes, I was planning to use some combination of
> LIKE searches; the database is small enough that this doesn't take very
> much time, and we don't expect the number of searchable records (or
> columns within those records) to be all that large. The thing is, the
> people running the site want searches to work on what I'm calling (for
> lack of a better term) "bare" letters.  That is, if the user searches for
> "n", then the search should also match Spanish words containing "ñ".  I'm
> told by Spanish-speaking members of the team that this is how they would
> expect searches to work.  However, when I just did a quick test using a
> UTF-8 encoded 9.0 database, I found that PostgreSQL didn't  see the two
> characters as identical.  (I must say, this is the behavior that I would
> have expected, had the Spanish-speaking team member not said anything on
> the subject.) So my question is whether I can somehow wrangle PostgreSQL
> into thinking that "n" and "ñ" are the same character for search purposes,
> or if I need to do something else -- use regexps, keep a "naked,"
> searchable version of each column alongside the native one, or something
> else entirely -- to get this to work. Any ideas?
> Thanks,
> Reuven



What kind of "client" are the users using?  I assume you will have some kind 
of user interface. For me this is a typical job for a user interface. The 
number of letters with "equivalents" in different languages are extremely 
limited, so a simple matching routine in the user interface should give you a 
way to issue the proper query.

Uwe

-- 
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] Searching for "bare" letters

[planas]

On Sun, 2011-10-02 at 01:25 +0200, Reuven M. Lerner wrote:

> Hi, everyone.  I'm working on a project on PostgreSQL 9.0 (soon to be
> upgraded to 9.1, given that we haven't yet launched).  The project
> will involve numerous text fields containing English, Spanish, and
> Portuguese.  Some of those text fields will be searchable by the user.
> That's easy enough to do; for our purposes, I was planning to use some
> combination of LIKE searches; the database is small enough that this
> doesn't take very much time, and we don't expect the number of
> searchable records (or columns within those records) to be all that
> large.
> 
> The thing is, the people running the site want searches to work on
> what I'm calling (for lack of a better term) "bare" letters.  That is,
> if the user searches for "n", then the search should also match
> Spanish words containing "ñ".  I'm told by Spanish-speaking members of
> the team that this is how they would expect searches to work.
> However, when I just did a quick test using a UTF-8 encoded 9.0
> database, I found that PostgreSQL didn't  see the two characters as
> identical.  (I must say, this is the behavior that I would have
> expected, had the Spanish-speaking team member not said anything on
> the subject.)
> 
> So my question is whether I can somehow wrangle PostgreSQL into
> thinking that "n" and "ñ" are the same character for search purposes,
> or if I need to do something else -- use regexps, keep a "naked,"
> searchable version of each column alongside the native one, or
> something else entirely -- to get this to work.
> 

Could you parse the search string for the non-English characters and
convert them to the appropriate English character? My skills are not
that good or I would offer more details.

> Any ideas?
> 
> Thanks,
> 
> Reuven
> 
> 
> 
> -- 
> Reuven M. Lerner -- Web development, consulting, and training
> Mobile: +972-54-496-8405 * US phone: 847-230-9795
> Skype/AIM: reuvenlerner



-- 
Jay Lozier
jsloz...@gmail.com

[GENERAL] Searching for "bare" letters

[Reuven M. Lerner]

  
  
Hi, everyone.  I'm working on a project on PostgreSQL 9.0 (soon
  to be upgraded to 9.1, given that we haven't yet launched).  The
  project will involve numerous text fields containing English,
  Spanish, and Portuguese.  Some of those text fields will be
  searchable by the user.  That's easy enough to do; for our
  purposes, I was planning to use some combination of LIKE searches;
  the database is small enough that this doesn't take very much
  time, and we don't expect the number of searchable records (or
  columns within those records) to be all that large.
The thing is, the people running the site want searches to work
  on what I'm calling (for lack of a better term) "bare" letters. 
  That is, if the user searches for "n", then the search should also
  match Spanish words containing "ñ".  I'm told by Spanish-speaking
  members of the team that this is how they would expect searches to
  work.  However, when I just did a quick test using a UTF-8 encoded
  9.0 database, I found that PostgreSQL didn't  see the two
  characters as identical.  (I must say, this is the behavior that I
  would have expected, had the Spanish-speaking team member not said
  anything on the subject.)
So my question is whether I can somehow wrangle PostgreSQL into
  thinking that "n" and "ñ" are the same character for search
  purposes, or if I need to do something else -- use regexps, keep a
  "naked," searchable version of each column alongside the native
  one, or something else entirely -- to get this to work.
Any ideas?
Thanks,
Reuven

 
-- 
Reuven M. Lerner -- Web development, consulting, and training
Mobile: +972-54-496-8405 * US phone: 847-230-9795
Skype/AIM: reuvenlerner

  

Re: [GENERAL] SQL Help - Finding Next Lowest Value of Current Row Value

[Chris Travers]

On Sat, Oct 1, 2011 at 2:30 PM, Jeff Adams  wrote:
> Greetings,
>
> I have a large table (~19 million records). Records contains a field
> identifying a vessel and a field containing an time (epoch). Using the
> current rows vessel and time values, I need to be able to find the next
> lowest time value for the vessel and use it to compute how much time has
> elapsed between the records. I have tried a scalar subquery in the SELECT,
> which works, but it runs quite slowly. Is there an alternative approach that
> might perform better for this type of query. Any information would be
> greatly appreciated. Thanks...
>
How many rows per vessel?

Either way it seems to me we are talking about selecting the current
record, and then selecting the max of another record within a
timeframe.  I would probably use a plpgsql function that would do this
in two stages, ideally being able to do an index scan twice

Best Wishes,
Chris Travers

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

[GENERAL] Log-Info Replication

[Andreas]

Hello.

I tried to use replication with version 9.1. But i get always the Error
FATAL:  falsche Pr?fsumme in Kontrolldatei

I think, it would be good get a hint on a server with 'hot_standby = on'
"if you try to replicate: it is not possible to replicate between 32 and 
64bit"


thanks
andreas

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

[GENERAL] SQL Help - Finding Next Lowest Value of Current Row Value

[Jeff Adams]

Greetings,

I have a large table (~19 million records). Records contains a field
identifying a vessel and a field containing an time (epoch). Using the
current rows vessel and time values, I need to be able to find the next
lowest time value for the vessel and use it to compute how much time has
elapsed between the records. I have tried a scalar subquery in the SELECT,
which works, but it runs quite slowly. Is there an alternative approach that
might perform better for this type of query. Any information would be
greatly appreciated. Thanks...

Jeff



-- 
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 to find freak UTF-8 character?

[Leif Biberg Kristensen]

On Saturday 1. October 2011 21.29.45 Andrew Sullivan wrote:
> I see you found it, but note that it's _not_ a spurious UTF-8
> character: it's a right-to-left mark, ans is a perfectly ok UTF-8 code
> point.

Andrew,
thank you for your reply. Yes I know that this is a perfectly legal UTF-8 
character. It crept into my database as a result of a copy-and-paste job from 
a web site. The point is that it doesn't have a counterpart in ISO-8859-1 to 
which I regularly have to export the data.

The offending character came from this URL: 


and the text that I copied and pasted from the page looks like this in the 
source code:

Aslaug Steinarsdotter Fjågesund  ‎(I2914)‎

I'm going to write to the webmaster of the site and ask why that character, 
represented in the HTML as the ‎ entity, has to appear in a Norwegian web 
site which never should have to display text in anything but left-to-right 
order.
 
> If you need a subset of the UTF-8 character set, you want to make sure
> you have some sort of constraint in your application or your database
> that prevents insertion of anything at all in UTF-8.  This is a need
> people often forget when working in an internationalized setting,
> because there's a lot of crap that comes from the client side in a
> UTF-8 setting that might not come in other settings (like LATIN1).

I don't want any constraint of that sort. I'm perfectly happy with UTF-8. And 
now that I've found out how to spot problematic characters that will crash my 
export script, it's really not an issue anymore. The character didn't print 
neither in psql nor in my PHP frontend, so I just removed the problematic text 
and re-entered it by hand. Problem solved.

But thank you for the idea, I think that I will strip out at least any ‎ 
entities from text entered into the database.

By the way, is there a setting in psql that will output unprintable characters 
as question marks or something?

regards, Leif.

-- 
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] bytea columns and large values

[Dmitriy Igrishin]

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 would of course be crafting
> >> a carefully generated client in C, using libpqtypes, which is the gold
> >> standard for sending bytea against which all others should be judged.
> >
> > I am sorry, but why the Large Objects interface sucks?
>
> hm, good question. well, it's not sql and is outside the type system.
> imo, bytea is really the way to go for sending binary.   also, if it
> turns out that we need to add features to send large pieces of data,
> it should not be specific to bytea.
>
But at the moment, the only way to put/get the data piecewise
is to use LOBs.

>
> merlin
>
> merlin
>



-- 
// Dmitriy.

Re: [GENERAL] bytea columns and large values

[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 would of course be crafting
>> a carefully generated client in C, using libpqtypes, which is the gold
>> standard for sending bytea against which all others should be judged.
>
> I am sorry, but why the Large Objects interface sucks?

hm, good question. well, it's not sql and is outside the type system.
imo, bytea is really the way to go for sending binary.   also, if it
turns out that we need to add features to send large pieces of data,
it should not be specific to bytea.

merlin

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] How to find freak UTF-8 character?

[Andrew Sullivan]

On Sat, Oct 01, 2011 at 07:55:01AM +0200, Leif Biberg Kristensen wrote:
> I've somehow introduced a spurious UTF-8 character in my database. When I try 
> to export to an application that requires LATIN1 encoding, my export script 
> bombs out with this message:
> 
> psycopg2.DataError: character 0xe2808e of encoding "UTF8" has no equivalent 
> in 
> "LATIN1"

I see you found it, but note that it's _not_ a spurious UTF-8
character: it's a right-to-left mark, ans is a perfectly ok UTF-8 code
point.

If you need a subset of the UTF-8 character set, you want to make sure
you have some sort of constraint in your application or your database
that prevents insertion of anything at all in UTF-8.  This is a need
people often forget when working in an internationalized setting,
because there's a lot of crap that comes from the client side in a
UTF-8 setting that might not come in other settings (like LATIN1).

Best,

A

-- 
Andrew Sullivan
a...@crankycanuck.ca

-- 
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 to install 9.1 on non-graphical server?

[Devrim GÜNDÜZ]

On Fri, 2011-09-23 at 02:46 +0200, Andreas wrote:
> is there a way to install the EnterpriseDB V9.1 release on a server 
> without X-Windows? 

You can run the installer with

 --mode text 

parameter.

Regards,
-- 
Devrim GÜNDÜZ
Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org  Twitter: http://twitter.com/devrimgunduz


signature.asc
Description: This is a digitally signed message part

Re: [GENERAL] bytea columns and large values

[Dmitriy Igrishin]

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
> standard for sending bytea against which all others should be judged.
>
I am sorry, but why the Large Objects interface sucks?



-- 
// Dmitriy.