Re: [GENERAL] Slides for PGCon2016; "FTS is dead ? Long live FTS !"

[Kaare Rasmussen]

On 2016-05-31 13:24, Stefan Keller wrote:


> We chose RUM just because there are GIN and VODKA :)
> But some people already suggested several meanings like Really 
Useful iMdex :)

> We are open for suggestion.

So I propose: "Ranking UMdex" ;-)



How about "Russian Unbelievable Magic"? Or just "RUssian Magic" if you 
do believe...


/kaare


--
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] json indexing and data types

[Kaare Rasmussen]

Hi Oleg

This is known problem, that's why we stop developing jsquery and are 
working on sql-level query language for jsonb, then you'll use all 
power and extendability of SQL.  The idea is to use power of 
subselects and unnest to unroll jsonb to sql level.

There is presentation at pgconf.eu  on this
https://wiki.postgresql.org/images/4/4e/Createam.pdf, see slide #27


This is very interesting. Thanks for the update. And to all who answered 
this topic, sorry for awoling. I just got busy, but thanks for all the 
replies, I got something to think about.



But I'm afraid it'll come to 9.6.


I'll hope it comes in 9.6. I'll definitely look forward to that.

/kaare

Re: [GENERAL] json indexing and data types

[Kaare Rasmussen]

On 2015-12-03 05:04, Tom Lane wrote:
Yeah. The problem here is that a significant part of the argument for 
the JSON/JSONB datatypes was that they adhere to standards (RFC 7159 
in particular). I can't see us accepting a patch that changes them 
into JSON-plus-some-PG-enhancements.


Would be nice for my specific need, but probably wouldn't do a lot of 
good in the long run.


(Having said that, it sure looks to me like JSON's idea of a number is 
float/numeric, not merely int. Are you sure you need more capability 
in that department, and if so what exactly?) 


Hmm, I think you're right, having just tried some conversions. But 
still, I would have to rely on vodka (or similar) knowing this, I guess?


/kaare


--
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] json indexing and data types

[Kaare Rasmussen]

On 2015-12-03 02:06, Merlin Moncure wrote:
I feel your pain.  jsquery is superb for subdocument searching on 
*specific* subdocuments but range searching is really limited.  Value 
searching is there for numerics but dates and text range searching are 
not present.  We also have to understand that you are asking the index 
to make assumptions about the json that are not clear from the 
structure itself (such 


I thought that text range searching (using indexes) was available in pg, 
at least with vodka ?
Some might say that you're better off using a dedicated json searching 
server like solr but these systems aren't magic; they will quickly 
boil down to a brute force search in the face of complex queries, and 
they have lots of other problems in my experience (starting with, lack 
of proper transactions and painfully slow insertion of large 
documents).  Other people recommend them; I don't.


They come with their own set of problems. Including not being able to be 
part of a where clause. The json data may not be the only thing you want 
to limit your selection with.


/kaare



--
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] json indexing and data types

[Kaare Rasmussen]

On 2015-12-03 01:04, Jim Nasby wrote:
We have a client that has a similar (though also a bit different) 
need. Specifically, they get an XML document that has element 
attributes that tell you what data type the element should contain. We 
convert the XML to JSON (easy thanks to plpython), which produces a 
bunch of nested JSON objects (typed as specifically as possible in 
JSON). The XML attributes get turned into items in an object. So


OK, AFAIUI, you added the schema to each row. I think that I have fewer 
variations, so perhaps the information would live better outside, but 
that's a detail. Turning them into tables and views is a good way to 
represent the indexable data. Functionally, it seems to me to be almost 
the same as functional indexing, but much more transparent, and easier 
to write a query for,


Are you in control of the JSON itself, and are the number of 
permutations known in advance? It might be that something like table 
inheritance is a better solution...


Yes, I can alter the db specification. Not sure how table inheritance 
would help, though?


/kaare


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

[GENERAL] json indexing and data types

[Kaare Rasmussen]

Hi

As json essentially only has three basic data types, string, int, and 
boolean, I wonder how much of this - to index, search, and sort on 
unstructured data -  is possible. I guess part of the answer would be 
'jsquery and vodka', but let me describe the problem first.


The basics is, that I have a column with what is essentially json data; 
a number of data structures of different depths. Perhaps 10 - 30 top 
levels, and probably no more than 3, max 4 levels deep. In total there 
are some hundred thousands of rows in each table. It would probably be 
best stored as jsonb. Right now it's text, because it's only used by the 
application itself.


It would be incredibly useful to add an index to this column, and to be 
able to search, using the index, on arbitrary elements. This part seems 
already there, with jsquery.


The hard part is that some of the data items really have another type. 
There are dates and floating points, as the most important ones. And the 
really hard part is that sorting and range searches are important, 
especially for these two types. Having dates is iso-format, and 
left-padding floats with zeros is a low tech solution, and especially 
the latter is not very efficient.


The solution might be to add functional indexes for these data items, 
but it's cumbersome and not easily maintainable. If a one-stop solution 
is in the works, or already there, it could save a lot of time.


/kaare


--
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] hstore syntax

[Kaare Rasmussen]

On 12/08/2013 11:45 AM, Magnus Hagander wrote:

If it's just for a single value, you can just use

SELECT hstore('key', s.part)

It also takes array (either one big array, or one array of keys and 
one array of values).




Super! I knew I was missing the obvious.



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

[GENERAL] hstore syntax

[Kaare Rasmussen]

Hi

Trying to write a sql function to return hstore generated from a select. 
But I'm thinking there must be a better way.


SELECT 'key => "' || s.part || '"')::hstore

is neither pretty nor secure. At least I need to escape any '"' in 
s.part. I'll do so if there's no better way to write this (?)


Also, it seems there's no length or size function for hstore? This is 
what I've got working now array_length(akeys(s.hstore_field), 1). It 
seems to be a rather long way to do it, but it works.



--
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] Tree structure

[Kaare Rasmussen]

Hi Rémi

Hey sorry if my answer is stupid,
but there is an extension for array, even if it is limited to int (but 
int could be indexes of row)

It's named http://www.postgresql.org/docs/9.3/static/intarray.html
It provides essential function, although lacking some (I 
re-implemented union of array with disjoint result).

I think this extension uses indexes



Thanks for your answer. If there were a similar strarray, it would be 
top nice :-)



--
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] Tree structure

[Kaare Rasmussen]

Hi Merlin


On Thu, Oct 10, 2013 at 1:00 AM, Kaare Rasmussen  wrote:

I'm quite surprised there seem to be no way in core to treat an array as an
array. Using @> treats it as a set, AFAICT.

can you elaborate on that?

merlin


To me, an array is a vector (or a vector of vectors). So I'm looking for 
an operator where


ARRAY[1,4,3] doesn't contain ARRAY[3,1] and
ARRAY[2,7] isn't contained by ARRAY[1,7,4,2,6] (but ARRAY[1,7,4] is)

IOW order matters to me, but not to the array operators mentioned in 
http://www.postgresql.org/docs/9.3/static/functions-array.html. Note 
that index support is important.



--
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] Tree structure

[Kaare Rasmussen]

Sorry, got tangled up in this thing called 'real life'.


If I understand you correctly, you want a prefix match, and sure there's
a PostgreSQL extension for that:


OK, that seems to do the job, thanks a lot. The only small quibble is 
that it's an extension.


I'm quite surprised there seem to be no way in core to treat an array as 
an array. Using @> treats it as a set, AFAICT.




--
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] Tree structure

[Kaare Rasmussen]

Hi Alban

4. Using a recursive common table expression (CTE). 
http://www.postgresql.org/docs/9.2/static/queries-with.html 


Yes, you're right. In fact that's what I'm testing a way to replace, as 
I'm not confident in the performance in all situations. My fault 
entirely; I should have told so from the start.





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

[GENERAL] Tree structure

[Kaare Rasmussen]

Hi

I'm trying to determine the best way to represent a simple tree 
structure (like a file/dir tree or a uri path). I guess that's done a 
zillion times before; I just don't seem to be able to find the right 
solution. I have one special request, that I'd like to find all 
'shorter' paths, i.e. given 'a/b/c/d' it'll find


a
a/b
a/b/c
- but not
b
a/c
b/a

There are a number of options to test.

1. As strings
  There's no dedicated function (@>)
  WHERE clause should read something like 'a/b/c/d' LIKE column || '%', 
which is both ugly and (I guess) non indexable

  Perhaps regex indexes would work, but not efficient and not optimal

2. As array of strings
  My favorite, would be elegant. A GIN index on the whole array would 
make for fast performance

  Alas @> treats the arrays as a set, not an array
  WHERE col @> 'a/b/c/d' would find all of the above rows, including a, 
a/c, b/a, etc.


3. ltree contrib
  The only option that actually works and uses index
  @> works as I want it to.
  But the single segments can only be alphanumeric and underscore
  ltree only supports GIST
  there's a length limit.

The last option is the one I'm using right now, but I hope that somebody 
can point out where I'm wrong with regards to the other options, or tell 
me that there is a better solution somewhere I didn't look.



--
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] State of Beta 2

[Kaare Rasmussen]

> No can do, unless your intent is to force people to work on pg_upgrade
> and nothing else (a position I for one would ignore ;-)).  With such a
> policy and no pg_upgrade we'd be unable to apply any catalog changes at
> all, which would pretty much mean that 7.5 would look exactly like 7.4.

Not sure about your position here. You claimed that it would be a good idea to 
freeze the on disk format for at least a couple of versions. Do you argue 
here that this cycle shouldn't start with the next version, or did you 
reverse your thought ?

If the former, I think you're right. There are some too big changes close to 
being made - if I have read this list correctly. Table spaces and PITR would 
certainly change it.

But if the freeze could start after 7.5 and last two-three years, it might 
help things.

-- 
Kaare Rasmussen--Linux, spil,--Tlf:3816 2582
Kaki Datatshirts, merchandize  Fax:3816 2501
Howitzvej 75   Åben 12.00-18.00Email: [EMAIL PROTECTED]
2000 FrederiksbergLørdag 12.00-16.00   Web:  www.suse.dk

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] State of Beta 2

[Kaare Rasmussen]

Hi

> Yes, it's been discussed to death, and it isn't easy.  See the archives

That's what I thought.

> "interesting" category.  It is in the category of things that will only
> happen if people pony up money to pay someone to do uninteresting work.
> And for all the ranting, I've not seen any ponying.

Just for the record now that there's an argument that big companies need 24x7 
- could you or someone else with knowledge of what's involved give a 
guesstimate of how many ponies we're talking. Is it one man month, one man 
year, more, or what?

Just in case there is a company with enough interest in this matter.

Next question would of course be if anyone would care to do it even though 
they're paid, but one hypothetical question at the time :-)

-- 
Kaare Rasmussen--Linux, spil,--Tlf:3816 2582
Kaki Datatshirts, merchandize  Fax:3816 2501
Howitzvej 75   Åben 12.00-18.00Email: [EMAIL PROTECTED]
2000 FrederiksbergLørdag 12.00-16.00   Web:  www.suse.dk

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] State of Beta 2

[Kaare Rasmussen]

> He is right, it might be a good idea to head this problem 'off at the
> pass'. I am usually pretty good at predicting technilogical trends. I've

Well, the only solution I can see is to make an inline conversion tool that 
knows about every step from earlier versions.

I believe this has been discussed before, but it does not seem to be a small 
or an easy task to implement.

-- 
Kaare Rasmussen--Linux, spil,--Tlf:3816 2582
Kaki Datatshirts, merchandize  Fax:3816 2501
Howitzvej 75   Åben 12.00-18.00Email: [EMAIL PROTECTED]
2000 FrederiksbergLørdag 12.00-16.00   Web:  www.suse.dk

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

[GENERAL] 7.4 defaulting to ipv6 ?

[Kaare Rasmussen]

Sorry if this is obvious to you, but it's not obvious to me.

In 7.4 this

psql -h 127.0.0.1 test and
psql -h localhost test

will be authenticated as ipv6 addresses. As long as you know, it's OK, but is 
this intentional?

-- 
Kaare Rasmussen--Linux, spil,--Tlf:3816 2582
Kaki Datatshirts, merchandize  Fax:3816 2501
Howitzvej 75   Åben 12.00-18.00Email: [EMAIL PROTECTED]
2000 FrederiksbergLørdag 12.00-16.00   Web:  www.suse.dk

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

[GENERAL] PostgreSQL consultant

[Kaare Rasmussen]

Hi

I tried to find the page with PostgreSQL consultants. After 10 minutes I gave 
up.

I believe that I've heard that there is such a page, but maybe I'm wrong? Or 
maybe I just suck at searching the PostgreSQL site ?

-- 
Kaare Rasmussen--Linux, spil,--Tlf:3816 2582
Kaki Datatshirts, merchandize  Fax:3816 2501
Howitzvej 75   Åben 12.00-18.00Email: [EMAIL PROTECTED]
2000 FrederiksbergLørdag 12.00-16.00   Web:  www.suse.dk

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[GENERAL] Re: Is PostgreSQL ready for mission criticalapplications?

[Kaare Rasmussen]

> But I am not imagining the random "I have rolled back the current
transaction
> and am going to terminate your database system connection and exit."
messages.

I'm wondering if you ever reported these problems to this list or the
the hackers list? I've been reading both regularily and don't recall
seeing this descussed before, but maybe I'm wrong.

Generally I find the responsiveness from the development team way better
than any commercial products. _All_ problem reports are treated with
concern. So if you didn't report them before, please take the time to
document your experience and send the problem report to the correct
place.

[GENERAL] Dome

[Kaare Rasmussen]

Anybody that knows anything about Dome ( http://www.htc.honeywell.com/dome/
) ?

It seems to be a quite extensive CASE tool. Can I use it to build a
Database layout and let it generate sql statements to create the database??

[GENERAL] The closest match

[Kaare Rasmussen]

I'm doing a db layout in Logic Works' Erwin. It can generate SQL
statements to create the database. Of course it doesn't include support
for PostgreSQL (yet) but I wondered which syntax was closest of the
supported ones.

These are supported:
DB2
SQL Server
Rdb
Oracle
SQLBase
Watcom/SQL Anywhere
Ingres
Sybase
AS/400
Informix
Progress
Teradata
Red Brick
InterBase

Also these client type "db"'s
Clipper
FoxPro
dBASE III
dBASE IV
Access
Paradox

I've tried Access (too few data types) and Oracle (Ugh. Everything in
capitals). Ingres seems to be close, but...?

Re: [GENERAL] July 1999 issue of Sys Admin

[Kaare Rasmussen]

> The July issue of sys admin has a feature on Perl for DBAs.
> This is actually better coverage than you get in most perl

> Shall I fax you a copy?

Yes, please :-)

I'd like to ask if it's on the web?

[GENERAL] Outer joins

[Kaare Rasmussen]

Going through the documentation I can only find little about outer
joins. One statement is in the Changes doc about including syntax for
outer joins, but there doesn't seem to be implemented any code after
that.

Is it true that there's no outer joins yet? Any plans? Btw. what is the
syntax for outer joins. I know only Oracle's (+) operator.
 

[GENERAL] WebDB

[Kaare Rasmussen]

I've made a CGI script can insert, update and delete rows in a table. It
started as a project directed towards a specific table, but I soon
realized that it is possible to do a generic CGI script to handle any
table.

So I've done that. Now I want someone to test it. Notice however that it
is still considered early beta, but it works for me.

WebDB
- Uses PostgreSQL now, but should be able to use any DB supported by
Perl DBI.
- Inserts, updates and deletes rows in any table in any database
- Is configurable by simple configuration files. One file pr. page; one
file pr. language (Currently there is English and Danish)
- Is beta software
- Only supports one table and one key field pr. page in this version I
have plans for more, but that may depend on feedback.

If you'd  like to try it, please email me and I'll send a tar file. It's
only a few KB.

Re: [GENERAL] Calcuate percentage.

[Kaare Rasmussen]

> P.S.  In oracle, I'd use a sub-query:
> 
> SELECT var, COUNT(*) / total_count 
>   FROM temp, 
>( SELECT COUNT(*) AS total_count 
>FROM temp
>)
> GROUP BY var;

I thought that subqueries were allowed in PostgreSQL after 6.2?

Re: [GENERAL] Calcuate percentage.

[Kaare Rasmussen]

> Can I somehow get the total number of rows in
> a function?

 create function numRows() returns int4 
 as 'select count(*) from ' 
 language 'sql'; 

> select var1, count(*) / numRows() * 100 from table1 group by var1;

maybe this is better

select var1, (count(*) * 100) / numRows() from table1 group by var1;

[GENERAL] Table as parameter in function

[Kaare Rasmussen]

As I tried to make a function for this percent calculation I fell over
the problem of passing a table to the function. I'd like to make this
function:

create function NumRows(table) returns int4
as 'select count(*) from $1'
language 'sql';

Is this possible?

[GENERAL] Tree structure

[Kaare Rasmussen]

I can't figure this one out. I need a tree structure like this

Number  Pointer
10
21
31
42
50
61
75

This should somehow show up like this
Number
1
2
4
3
6
5
7

The whole excercise is because I'd like to show a tree structure:

1
- 2
- - 4
- 3
- 6
5
- 7

Is this possible with PostgreSQL?

[GENERAL] VIEW's on UNION's

[Kaare Rasmussen]

I miss . I know the answer is that I can write them myself,
but I don't know where to start or end.

See this construct:
CREATE TABLE gl-amount (
glam_nr int,
period date,
entry_amount numeric(9,0),
PRIMARY KEY (glam_seq,period)
);

CREATE VIEW glam_curmth_v AS
SELECT entry_amount AS amount1 FROM gl-amount
WHERE date_part(year', period) = date_part('year', now::datetime)
AND date_part('month', period) = 1
UNION SELECT entry_amount AS amount2 FROM gl-amount
WHERE type = 'S' AND  period = 2;  

   SELECT entry_amount AS amount1 FROM gl-amount
WHERE date_part('year', period) = date_part('year', 'now'::datetime)
AND date_part('month', period) = 1
UNION SELECT entry_amount AS amount2 FROM gl-amount
WHERE date_part('year', period) = date_part('year', 'now'::datetime)
AND date_part('month', period) = 2
...

Can it be done without the use of VIEW's on UNION's?

[GENERAL] Error

[Kaare Rasmussen]

I got an error when accessing a view. The view looks like this:

CREATE TABLE ar_contacts(
contact_seq int,
update_date date,
period  date,
paidfloat,
sales   float,
last_date   date,
due float,
PRIMARY KEY (contact_seq, period)
);
CREATE VIEW ar_sales90_v AS
SELECT contact_seq, SUM(sales) AS sales90 FROM ar_contacts WHERE
period >= date(date_trunc('month','now'::date) - '2 month'::timespan)
GROUP BY contact_seq;

If I 
SELECT * FROM ar_sales90_v WHERE contact_seq = 1; 
then everything is fine. I get what I expect:

contact_seq|sales90
---+---
  1|  6
(1 row)

If I do this:
SELECT sales90 FROM ar_sales90_v WHERE contact_seq = 1; 
I get this result:

pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally before or while
processing the request. We have lost the connection to the backend, so
further processing is impossible.  Terminating.

The backend didn't die; I can restart psql and do my queries.

[GENERAL] Views

[Kaare Rasmussen]

How can I see a view in psql after having created it? If I enter 
\d  I can see the fields, but how can I see what makes up the
view? Best if the original CREATE statement could be listed somehow.

[GENERAL] 6.4 ?

[Kaare Rasmussen]

I've used PostgreSQL before, but has just joined this mailing list
again after having searched www.postgresql.org for any information
about 6.4. There's no mention of what is news in 6.4 anywhere to be
found. Why not? What _is_ new?