Torsten Förtsch | 19 Apr 22:51 2014
Picon
Picon

Disable an index temporarily

Hi,

an index can be INVALID (pg_index.indisvalid=false).

I want to temporarily disable an index so that it won't be used to
access data but will still be updated.

Can I simply set pg_index.indisvalid=false and later turn it true again?

Thanks,
Torsten

--

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

Andy Colson | 19 Apr 17:04 2014
Picon

Re: Could use some advice on search architecture

On 04/19/2014 06:26 AM, Ron Pasch wrote:
>  > - It should be possible to search for products and provide properties
>  > that the product SHOULD have, not must have.
>>
>> I don't understand this.  Say you have a sprocket in red and green. Do you want to search for:
>>
>> select * from product where name = 'sprocket' and (color = 'red' or color = 'green')
>>
>> Or do you want something else?  Does the user say they'd "prefer" blue, but will take whatever else you have?
>>
>> Do you search for some properties exactly and some "preferred"?
>>
>> Perhaps you could describe a little more how you want to query the database?  Or, maybe, what your user's
are searching for?
>>
>> -Andy
>>
>>
>
> Yes, the user can prefer certain properties and the products that match most of the properties should be in
the top of the results, but if a product doesn't match all of them but just some of them, they should still be
returned, but lower in the results.
>
> I'm seriously wondering if doing this solely with postgres is even possible without having long
execution times. I've done some tests with 5 million records and just doing the "or" construction you
mentioned above, which resulted in 600 to 900 ms queries and returning only those records of which all
properties match at least one selected value.
>
> I was thinking that perhaps using a search engine like lucene or sphinx would be more appropriate, but then
I wonder what I would exactly be indexing and how I would be querying that, but that's a question for a
(Continue reading)

Robin | 19 Apr 14:10 2014
Picon

Re: Could use some advice on search architecture

bottom post
On 19/04/2014 12:46, R. Pasch wrote:
On 19-4-2014 9:38, Robin wrote:

Well, given that there are known limited attributes, this is the type of application that really really suits a column oriented database, such as Sybase IQ (now sold by SAP). Its a neat product that scales. Great performance with drag'n'drop analytics.

Unless you can charm IQ out of SAP (it has been known to happen), you might have to look at some other techniques

So consider some binary data representation
Red - 1 (0000 0001)
Orange - 2 (0000 0010)
Yellow - 4 (0000 0100)
Green - 8 (0000 1000)
Blue - 16 (0001 0000)
Indigo - 32 (0010 0000)
Violet - 64 (0100 0000)

This way, you can encode several colours in 1 value
Red or Green or Indigo = 1 + 8  + 32 = 41 = 0010 1001


Robin


I stopped reading when I heard the word "sold by SAP" ;-) This project is solely build with open-source and freely available software.

I've been thinking about using a binary data representation but didn't come to a solution to this specific problem quite yet. Per property of a product, only one bit would be 1 and the rest would be 0. What would a query look like to match all products that have a bit in the correct position?

Say for instance these are a couple records (and yes, property values can be null as well)

title, property1, property2, property3
================================
product1, 0000 0001, 0000 0010, NULL
product2, 0000 0100, 0100 0000, 0010 0000
product3, 0010 0000, 0010 0000, 0100 0000

Say that I would like to retrieve the products that either have property1 as 0010 0000, 1000 000 or 0000 0001. Combined that would be 0010 1001 and would have to match product1 and product3 as they both have their individual bit matching one of the bits being asked for. What would a where statement look like using this type of binary representation?

If that would be fairly simple to do and fast (most important factor) then I could do an OR construction on all property columns and have something count the amount of properties that actually matched. Is that something you can do with a binary operator of some sort as well? Count the amount of overlapping bits?

Say for instance I have a binary value of 0110 0101 and another binary value of 1100 0100, how could I found out how many bits matched? (in this case the number of matching bits would be 2)


I understand the reluctance to pay SAP-style rates, as a longtime DB user, I have learned some 'charm' techniques.

However, I poked around a bit for alternatives, as I do like the column-oriented approach, and found something called - MonetDB - it apparently has a column-store db kernel, and is open source - I suggest you have a look, if it does what it says on the label, then it looks like a find.

There is a discussion of bitmask-trickiness here also dealing with colours

Robin


Gunnar "Nick" Bluth | 19 Apr 12:44 2014
Picon

Patched odbc_dfw for PG >= 9.2

Heillo,

after stumbling across compile errors for odbc_fdw I realised it was 
still on the 9.1. level.

I've patched it, based on the tds_fdw code (so it's still read-only).

It does compile, and I think it is generally sane, however I'm _anything 
but a C hacker_, so whoever feels confident enough please review this, 
or even add the write capability :)

As I've been playing around with tds_fdw before and just wanted to see 
if I can get around my encoding issues with odbc_fdw, I will not be able 
to test it for a few days though.

It is available at
https://github.com/bluthg/odbc_fdw

According notes have been added to the Foreign_data_wrappers WIKI page 
as well (no need for more people to try compiling it ;-).

Cheers,

-- 
Gunnar "Nick" Bluth
RHCE/SCLA

Mobil   +49 172 8853339
Email: gunnar.bluth <at> pro-open.de
__________________________________________________________________________
In 1984 mainstream users were choosing VMS over UNIX.  Ten years later
they are choosing Windows over UNIX.  What part of that message aren't you
getting? - Tom Payne

--

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

Adrian Klaver | 18 Apr 19:43 2014

Re: Arduino SQL Connector

On 04/18/2014 09:39 AM, Tomas Vondra wrote:
> On 18 Duben 2014, 17:01, Adrian Klaver wrote:
>> On 04/18/2014 07:53 AM, Jan Wieck wrote:
>>> On 04/18/14 10:31, Steve Spence wrote:
>>>> Not a thing in that document about the Arduino. Just how to install
>>>> Postgres on a Raspberry Pi. My Postgres is on a hosted server at a ISP.
>>>
>>> You intend to have thousands of Arduino devices, incapable of doing any
>>> sort of encryption or other means of secure IP connections, directly
>>> connect to a database, that is hosted on a publicly accessible VPS?
>>>
>>> Maybe it is just me, but to me that design has DISASTER written in bold,
>>> red, 120pt font all over it.
>>
>> Jan,
>>
>> It is already established you do not like any part of this idea. Beating
>> the dead horse really does not accomplish anything.
>
> I don't think pointing out weaknesses of a proposed solution is equal to
> beating a dead horse.

Well the point is that the OP is already doing this with MySQL as the 
backend. He just wants to move to Postgres if possible.

>
> I see two potential issues here - security and excessive number of
> connections. Security, because while you can reasonably authenticate the
> client (e.g. using MD5 authentication), there's no way of encrypting the
> traffic. But if the data is not sensitive, this might be sufficient.
>
> Excessive number of connections, because if you keep one connection from
> each arduino device, and you have 1000s of devices ... you get the idea.
> But this might be resolved using pgbouncer + transaction pooling or so.

Valid concerns whether MySQL or Postgres or some other data store is 
used. Though as of now, from what I gather there are only 2 Arduinos 
being polled:

http://arduinotronics.blogspot.com/2014/04/arduino-based-sql-queries.html

>
> regards
> Tomas
>

-- 
Adrian Klaver
adrian.klaver <at> aklaver.com

--

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

Ron Pasch | 18 Apr 15:59 2014
Picon

Could use some advice on search architecture

Hello,

I'm contemplating what architecture I should use to make searching as 
fast as possible given the information available and the search 
requirements. Let me give some background first;

- The database contains products of can potentially have a lot of them 
(up to about 3 to 5 million)
- Each product has about 30 different properties defined about them. 
Things like what color they are etc. All these properties are enumerated 
choices, so for instance for color there is a list of available static 
never changing options of which one can be chosen for that product. This 
is the same for all those 30 properties. Currently they are stored as 
enumerated types (CREATE TYPE propertyvalue AS ENUM ('option1', 
'option2', etc..)
- It should be possible to search for products and provide properties 
that the product SHOULD have, not must have. For instance, for color, 
the search could specify that it should return products that are either 
red, blue or green.
- The products that match with the most properties should be in the top 
of the search results
- If different products match with the same amount of properties, the 
ordering should then be on the product that is most popular. There is 
information in the database (and if need be also in the same table) 
about how many times a product is sold.
- The results will be paginated per 15 products

The requirement is that these searches should be as fast as possible, 
with a maximum of about 200 ms time taken for a search query.

What would be the best approach to this if I were to do this in the 
database only? Should/can this be done with postgresql only or should I 
look into other types of technology? (Lucene? Sphinx? others?)

Any advice on this would be greatly appreciated.

Thx in advance!

Ron

--

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

Guillaume Drolet | 18 Apr 15:33 2014
Picon

Re: Cluster recovery - FATAL: database files are incompatible with server. Different PG_CONTROL_VERSION for cluster and server.

No the new test cluster isn't running anymore: I deleted it right
after I deleted my main one.

Re the error messages: I was calling pg_ctl with the option -D
<path_to_my_main_cluster_datadir>

2014-04-18 8:44 GMT-04:00 droletguillaume <droletguillaume <at> gmail.com>:
>
>
>
> Sent from Samsung Mobile
>
>
> -------- Original message --------
> From: Adrian Klaver
> Date:04-17-2014 20:36 (GMT-05:00)
> To: Guillaume Drolet ,pgsql-general <at> postgresql.org
> Subject: Re: [GENERAL] Cluster recovery - FATAL: database files are
> incompatible with server. Different PG_CONTROL_VERSION for cluster and
> server.
>
> On 04/17/2014 01:29 PM, Guillaume Drolet wrote:
>> Dear list users,
>>
>> For some tests, I installed a new cluster with different parameters
>> than the ones I had used a while ago to create the cluster I use for
>> my day-to-day activities (let's call it my main cluster). I used
>> initdb --no-locale -E UTF8 -D <new_data_dir>. Then I used pg_ctl -D
>> <new_data_dir> to start my new cluster. So far so good.
>>
>> Then, I realized that I wanted to add a database superuser with the
>> option -U to initdb so I decided to delete the newly created cluster.
>> Instead, I mistakenly deleted the data directory of my main cluster
>> (doh!), using Shift-Delete. I recovered the deleted data directory
>> using Panda Recovery Tool and copied it back into its location (the
>> location pointed to by $PGDATA).
>>
>> Now for the real problems: if I go in pgAdmin and try to connect to my
>> cluster, I get the "server not listening" message. I also tried going
>> to the Services and restarting postgresql but it says it is already
>> stopped and if I try to start it, I get a message that it has started
>> but then stopped because it was unused.
>>
>> If I try pg_ctl start, I get this message (my translation from
>> French):  FATAL: database files are incompatible with server. DETAIL:
>> Database cluster was initialized with a PG_CONTROL_VERSION ? 16795209
>> while the server was compiled with a PG_CONTROL_VERSION ? 937. HINT:
>> Looks like you need initdb.
>
>
> More thoughts.
>
> What happened to the new cluster?
>
> Is it still running?
>
> When you got the error messages where you pointing at the old or new
> cluster?
>
>>
>> I really don't know what to do and would appreciate any help, if
>> anything can be done to recover my databases. My PG version is 9.3.3
>> on a Windows 7 64-bit OS.
>>
>> Best regards and many thanks for your advice,
>>
>> Guillaume
>>
>>
>
>
> --
> Adrian Klaver
> adrian.klaver <at> aklaver.com

--

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

Jan Wieck | 18 Apr 06:02 2014

Re: Arduino SQL Connector

On 04/17/14 20:01, Steve Spence wrote:
> I'm trying to port my application from mysql to postgres (for the GIS
> supoort). I had hoped there would be wild enthsuiasm for such a
> project, as the Arduino community is extremely large, and this would
> push postgres into a wider audience. I expected it would take multiple
> people with varying backgrounds to accomplish the task. If you can do
> it on your own, great. I'll be happy to lend what knowledge I have of
> the Arduino and using the mysql connector -

This is a pattern I've seen for over a decade now. People think that 
randomly sprinkling "MySQL" into a discussion will generate "wild 
enthusiasm" for supporting "their attempt to port *their* appli ..." 
wait ... oh ... didn't work again?

Bummer!

Jan

> http://www.green-trust.org/lmanco/find.php
> Steve Spence, KK4HFJ
> Director, Green Trust
> http://www.green-trust.org
> Http://arduinotronics.blogspot.com
>
>
> On Thu, Apr 17, 2014 at 6:21 PM, Tomas Vondra <tv <at> fuzzy.cz> wrote:
>> On 17.4.2014 19:43, Steve Spence wrote:
>>> Oracle thought it was a good idea to put out a MySQL version, I
>>> figure there should be some effort to counter that here .....
>>
>> Really? I found no information about this on oracle.com or mysql.com,
>> except for a section in the discussion forum with ~20 posts. It's true
>> that the author is apparently a Senior Software Engineer at Oracle, but
>> this seems like a his own project. So much regarding the "Oracle
>> thought" part ...
>>
>> BTW I doubt the approach "Oracle does X => you should do X too" will be
>> fruitful here.
>>
>>> Need a Team lead on this, and I'll collaborate as much as I can on the
>>> Arduino / Networking side.
>>
>> I'm a bit confused. What do you mean by "team lead"? I was thinking that
>> maybe this would be a fun project for the holidays, refreshing my
>> Arduino-fu. But no way I'm interested in being a TL of anything.
>>
>> I was messing with the PostgreSQL protocol [1] recently, and writing a
>> code implementing something like what the mysql connector does would be
>> quite trivial. A day of work, maybe two if you know networking but not
>> the protocol.
>>
>> regards
>> Tomas
>>
>> [1] http://www.postgresql.org/docs/9.3/interactive/protocol.html
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>
>

-- 
Jan Wieck
Senior Software Engineer
http://slony.info

--

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

Guillaume Drolet | 17 Apr 22:29 2014
Picon

Cluster recovery - FATAL: database files are incompatible with server. Different PG_CONTROL_VERSION for cluster and server.

Dear list users,

For some tests, I installed a new cluster with different parameters
than the ones I had used a while ago to create the cluster I use for
my day-to-day activities (let's call it my main cluster). I used
initdb --no-locale -E UTF8 -D <new_data_dir>. Then I used pg_ctl -D
<new_data_dir> to start my new cluster. So far so good.

Then, I realized that I wanted to add a database superuser with the
option -U to initdb so I decided to delete the newly created cluster.
Instead, I mistakenly deleted the data directory of my main cluster
(doh!), using Shift-Delete. I recovered the deleted data directory
using Panda Recovery Tool and copied it back into its location (the
location pointed to by $PGDATA).

Now for the real problems: if I go in pgAdmin and try to connect to my
cluster, I get the "server not listening" message. I also tried going
to the Services and restarting postgresql but it says it is already
stopped and if I try to start it, I get a message that it has started
but then stopped because it was unused.

If I try pg_ctl start, I get this message (my translation from
French):  FATAL: database files are incompatible with server. DETAIL:
Database cluster was initialized with a PG_CONTROL_VERSION ? 16795209
while the server was compiled with a PG_CONTROL_VERSION ? 937. HINT:
Looks like you need initdb.

I really don't know what to do and would appreciate any help, if
anything can be done to recover my databases. My PG version is 9.3.3
on a Windows 7 64-bit OS.

Best regards and many thanks for your advice,

Guillaume

--

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

Steve Spence | 17 Apr 14:00 2014
Picon

Arduino SQL Connector

I'm using a MySQL library on my arduino that allows me to directly
connect to a MySQL database and perform SQL queries on the Arduino.

I need something similar for Postgres. Anyone have the knowledge and
interest to work with me?

Here is a sample of what I'm doing currently, and the results:

http://arduinotronics.blogspot.com/2014/04/arduino-based-sql-queries.html

http://green-trust.org/lmanco/find.php

Steve Spence, KK4HFJ
Director, Green Trust
http://www.green-trust.org
Http://arduinotronics.blogspot.com

--

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

David G Johnston | 17 Apr 02:28 2014
Picon

Re: any way for a transaction to "see" inserts done earlier in the transaction?

One possibility is that the INSERT is going to a different table (having the
same name but existing in a different schema) that is visible/default to the
function but not outside of it.  

Or the function on the server is not "current" and thus isn't doing what you
think it is.

> I do an insert via a function, which returns the new id, then later I try
> to SELECT on that id, and it doesn't find it.
> 
> Could it be because the insert is done inside a function?

Not by itself; but that factor could be interacting with something else to
cause the observed behavior.  As noted above functions are able to maintain
their own "schema" environment so what is executed in one and outside of one
can indeed target different physical objects - which has nothing to do with
transaction visibility.

Susan Cassidy-3 wrote
> It is a fairly large and complex Perl program, so no, not really.

Then you need to recreate a functionally similar, but limited, test case
that either exhibits the behavior in question or causes you to realize what
you are doing in wrong in the "large and complex Perl program".

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/any-way-for-a-transaction-to-see-inserts-done-earlier-in-the-transaction-tp5800432p5800459.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--

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


Gmane