[HACKERS] Amazon Redshift

[philip taylor]

Hackers,

do you think we should implement some of the functions offered by Amazon Redshift?



http://docs.aws.amazon.com/redshift/latest/dg/c_SQL_functions.html



Amazon Redshift is completely based on PostgreSQL, but they have implemented some features not currently available in PostgreSQL.
Given the fact that its a successful customer-driven commercial project, one can easily guess that the functions they implemented are useful and requested by many users.



Phil

Re: [HACKERS] Amazon Redshift

[philip taylor]

 do you think we should implement some of the functions offered by
 Amazon Redshift?

 http://docs.aws.amazon.com/redshift/latest/dg/c_SQL_functions.html

 Well, obviously we already have many of the functions linked from
 that page. I think that if you want to go through them and pick
 out a few that you think are particularly promising it could start
 a useful discussion. Pointing at a page that includes such things
 as CURRENT_DATE, the avg() aggregate, and the lead() window
 function is just going to waste the time of everyone who follows
 the link. It's not like all of them would be committed in the same
 patch anyway.

 At the risk stating the obvious, before we look at implementing them we 
 should see if Amazon would donate that effort.

 But as Kevin said, first step is discussion on what we don't have and whether 
 we want it.

Ok, this is a summary of what they have that we don't (of course, I could have 
missed something):

Date Functions

http://docs.aws.amazon.com/redshift/latest/dg/r_ADD_MONTHS.html
http://docs.aws.amazon.com/redshift/latest/dg/r_DATEADD_function.html
http://docs.aws.amazon.com/redshift/latest/dg/r_DATEDIFF_function.html
http://docs.aws.amazon.com/redshift/latest/dg/r_LAST_DAY.html
http://docs.aws.amazon.com/redshift/latest/dg/r_MONTHS_BETWEEN_function.html
http://docs.aws.amazon.com/redshift/latest/dg/r_NEXT_DAY.html

String Functions

http://docs.aws.amazon.com/redshift/latest/dg/REGEXP_COUNT.html
http://docs.aws.amazon.com/redshift/latest/dg/REGEXP_INSTR.html
http://docs.aws.amazon.com/redshift/latest/dg/r_STRTOL.html
http://docs.aws.amazon.com/redshift/latest/dg/crc32-function.html
http://docs.aws.amazon.com/redshift/latest/dg/FUNC_SHA1.html

JSON Functions

http://docs.aws.amazon.com/redshift/latest/dg/JSON_ARRAY_LENGTH.html
http://docs.aws.amazon.com/redshift/latest/dg/JSON_EXTRACT_ARRAY_ELEMENT_TEXT.html
http://docs.aws.amazon.com/redshift/latest/dg/JSON_EXTRACT_PATH_TEXT.html

Window functions

http://docs.aws.amazon.com/redshift/latest/dg/r_WF_RATIO_TO_REPORT.html
http://docs.aws.amazon.com/redshift/latest/dg/r_WF_MEDIAN.html


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

[HACKERS] array exclusion constraint

[Philip Taylor]

CREATE TABLE foo (
   x CHAR(32) PRIMARY KEY,
   y CHAR(32) NOT NULL,
   EXCLUDE USING gist ((ARRAY[x, y]) WITH )
);

ERROR:  data type character[] has no default operator class for access method 
gist
HINT:  You must specify an operator class for the index or define a default 
operator class for the data type.

Neither gist nor gin work. You can do that with integers using the intarray 
extension module.
Could you please suggest me a clean way to achieve the same result (using the 
char data type, not integers)?
Probably I could create a specific operator class or some other workaround, but 
the intarray implementation looks a bit complex.
Someone so kind to point me in the right direction?

Of course the following produces the same error:

CREATE TABLE bar (
   x VARCHAR[] NOT NULL,
   EXCLUDE USING gist (x WITH )
);

[HACKERS] Add SHA-3 (Keccak) support to pgcrypto

[Philip Taylor]

On Oct 2 the latest crypto hash function was announced by NIST [1]. I suggest 
that we include the new hash algorithm in pgcrypto for 9.3.
The Keccak site also has a reference implementation in C and Assembler [2]. It 
may take some effort to integrate the reference implementation as it contains 
several optimized backends for X86, X86_64, SIMD and various ARM platforms.

[1] http://www.nist.gov/itl/csd/sha-100212.cfm
[2] http://keccak.noekeon.org/