Na linguagem do lado do servidor Sybase, como meu código pode testar se está sendo executado no modo encadeado ou desencadeado (auto-commit)?

Examinar o status de @@TRANCOUNTnão é realmente conclusivo.

Eu entendo que deve haver uma maneira como, por exemplo, JDBC fornece getAutoCommit que é capaz de detectar se a conexão está no modo auto-commit (unchained) ou não. Mas como faço isso de dentro de um procedimento armazenado?

Eu encontrei esta pergunta , mas parece responder apenas ao SQL Server.

Estou lendo a descrição do datetimetipo de dados da documentação do Sybase ASE 15.7 :

As colunas datetime contêm datas entre 1º de janeiro de 1753 e 31 de dezembro de 9999. Os valores de datetime têm precisão de 1/300 segundo em plataformas que oferecem suporte a esse nível de granularidade.

Achei o acima muito confuso. Em primeiro lugar, este é o único tipo de dados para o qual é adicionada a advertência "em plataformas que suportam este nível de granularidade". O que exatamente isso significa e como descubro se minha plataforma é uma daquelas que "suporta esse nível de granularidade"?

Além disso, não está claro para mim o que significa efetivamente armazenar com precisão 1/300 de segundo. Estou acessando o banco de dados usando JDBC para o qual o único tipo plausível é java.sql.Timestamp . Esse tipo me permite recuperar com precisão de nanossegundos. Mas dado que a divisão por 300 requer dígitos infinitos no sistema decimal no caso geral, efetivamente um número fracionário de nanossegundos (com dígitos decimais infinitos) é necessário para manter um valor expresso como 1/300 de segundo. Portanto, isso significa que não consigo obter o valor armazenado no servidor sem perder alguma precisão, por mais insignificante que seja.

Por fim, quando executo a seguinte consulta:

SELECT convert(char(32), submission_date, 139) FROM some..table

Vejo valores como os seguintes:

Jan  6 2014 12:36:12.420000     
Sep 12 2013 13:44:57.100000     
Sep 10 2014 13:47:02.240000     
Sep 10 2014 13:47:07.850000     
Sep 10 2014 13:47:13.346000     
Sep 10 2014 13:47:19.033000     
Sep 10 2014 13:47:24.533000     
Sep 10 2014 13:47:30.030000     
Sep 10 2014 13:47:35.636000     
Sep 10 2014 13:47:41.136000     
Sep 10 2014 13:47:46.750000     
Sep 10 2014 13:47:52.240000     
Sep 25 2014 09:01:18.426000     

Esses valores parecem indicar que apenas milésimos de segundo inteiros são mantidos (não 1/300 de segundo - o que exigiria um número fracionário de milésimos). Se fosse o caso de o servidor armazenar internamente os valores "com precisão de 1/300 segundo", eu esperaria que a conversão para uma notação decimal usasse todas as casas decimais disponíveis (exceto para os casos de borda de 3/300 segundos, 30/300 segundos, 150/300 segundos e alguns outros que não requerem uma quantidade infinita de dígitos no sistema decimal).

Estou tentando entender a relação entre os conceitos de encadeamento de transações e autocommit . A confirmação automática geralmente é exposta em uma interface programática do lado do cliente. Por exemplo, em JDBC com o método Connection#setAutoCommit .

Ou seja, gostaria de saber se:

1. esses conceitos são simplesmente apelidos um do outro (e qual é a correspondência)

ou

2. esses conceitos são ortogonais e todas as 4 combinações fazem sentido e produzem resultados diferentes (e quais são esses resultados)

Há vários recursos que sugerem que o caso é 1. Os mesmos recursos parecem sugerir que unchainedcorresponde a autocommit on. Por exemplo , este post diz que:

set chained off (padrão no Sybase, = definir autocommit on)

Este post mais extenso diz da mesma forma:

Algumas interfaces de programação de banco de dados têm um modo de confirmação automática, também chamado de modo não encadeado. Nesse modo, cada instrução é uma transação e é confirmada após a execução. Se você deseja usar transações em seus aplicativos, você precisa usar o modo de confirmação manual ou o modo encadeado.

Este tem sido o meu próprio entendimento até agora. Por exemplo, sempre que tento executar (usando JDBC) um procedimento armazenado e me deparo com a seguinte mensagem:

java.sql.SQLException: O procedimento armazenado 'whatever' pode ser executado apenas no modo de transação não encadeada.

… então definindo no meu código:

conn.setAutoCommit(true);

… corrige o problema que parece confirmar que, de fato, unchained é o mesmo que autocommit .

No entanto, olhando para a documentação do Sybase ASE, lê-se:

O modo padrão, chamado modo não encadeado ou modo Transact-SQL, requer instruções de transação de início explícitas emparelhadas com instruções de transação de confirmação ou de reversão para concluir a transação.

Isso parece ser exatamente o oposto do autocommit - a menos que o texto acima seja entendido como tendo o seguinte significado:

O modo padrão, chamado modo não encadeado ou modo Transact-SQL, requer instruções de início de transação explícitas emparelhadas com instruções de transação de confirmação ou de reversão para concluir a transação quando se deseja colocar mais de uma única instrução em uma transação .

Finalmente, me deparei com este post que sugere que o encadeamento e o autocommit são principalmente ortogonais.

O sp_droptypeprocedimento pode ser usado para descartar um tipo definido pelo usuário.

Portanto, tenho várias dessas sp_droptypechamadas no meu script "limpo" que invoco usando sqsh -i. No entanto, não é possível invocar condicionalmente sp_droptypeapenas se o tipo de fato existir ou instruí-lo a ficar em silêncio se o tipo não existir.

Existe uma maneira de testar se um tipo existe ou não e invocar apenas sp_droptypese ele realmente existir? Isso resultaria em uma saída mais limpa.

No PostgreSQL pode-se criar um User Defined Type com um CHECK implícito fazendo o seguinte:

CREATE DOMAIN OrdinalT AS INTEGER CHECK (value >= 0);

No Sybase ASE 15.7, percebi que o seguinte é quase equivalente (exceto o CHECK):

sp_addtype OrdinalT, "INTEGER";

Como faço um CHECK parte da definição de tipo definido pelo usuário no Sybase?

Assim que me conecto a um servidor de banco de dados sem especificar um banco de dados específico, por exemplo, com:

sqsh -Usomeuser -Ssomeserver

… eu posso fazer um:

select db_name();

… para ver o banco de dados padrão para este usuário. Mas onde essas informações são mantidas e/ou como posso ver os bancos de dados padrão configurados para cada nome de usuário ou nome de login?

O diagrama a seguir descreve os principais conceitos do PostgreSQL muito bem:

Tenho que trabalhar com o Sybase ASE 15 e gostaria de saber quais são os conceitos semelhantes no Sybase. Por exemplo, pelo que entendi até agora, o Sybase carece do conceito de esquemas. Então algumas perguntas são:

• o Sybase ASE 15 tem o conceito de esquemas?
• qual é o limite para restrições de integridade referencial? Por exemplo, no PostgreSQL você pode ter chaves estrangeiras em esquemas (mas não em bancos de dados). Qual é a situação no Sybase ASE 15?

Eu gostaria de fazer pesquisa de texto completo em documentos XML.

No entanto, não consigo obter correspondências para valores de atributo (obtenho um erro de sintaxe se fornecer o valor de atributo completo, incluindo os caracteres :e /no exemplo abaixo). Além disso, não sei como especificar meus próprios delimitadores para o analisador.

Abaixo está o SSCCE:

Eu gostaria de ser capaz de selecionar o último componente do atributo value: attributevaluee para o analisador reconhecer :e /como delimitadores e assim produzir boo1, boo2e somecomo attributevaluelexemas.

DROP TABLE IF EXISTS xmldocument;
CREATE TABLE IF NOT EXISTS xmldocument (
i SERIAL NOT NULL,
content XML NOT NULL
);
ALTER TABLE xmldocument ADD PRIMARY KEY (i);

INSERT INTO xmldocument (content) VALUES
('<a>foo</a>')
,('<a boo=''boo1:boo2:boo3/boo4/some/attributevalue''>foo</a>')
;


-- matches both records as expected
SELECT * FROM xmldocument WHERE to_tsvector(CAST (content AS VARCHAR))@@'foo'; 

-- no match
SELECT * FROM xmldocument WHERE to_tsvector(CAST (content AS VARCHAR))@@'attributevalue'; 

-- no match
SELECT * FROM xmldocument WHERE to_tsvector(CAST (content AS VARCHAR))@@'boo2'; 

 -- no match
 SELECT * FROM xmldocument WHERE to_tsvector(CAST (content AS VARCHAR))@@'boo4'; 

Quanto ao motivo de não usar apenas ILIKE %etc. O motivo é que preciso otimizar usando um índice GIN e não acho que seja possível ou significativo criar um índice com valores VARCHAR simples.

*atualize após aceitar a resposta*

Com base na resposta aceita, todas as consultas a seguir funcionam conforme o esperado:

SELECT * FROM fts.xmldocument WHERE 
to_tsvector(regexp_replace(content::text,'[<>/]',' ','g')) @@ to_tsquery('foo');

SELECT * FROM fts.xmldocument WHERE
to_tsvector(regexp_replace(content::text,'[<>/]',' ','g')) @@ to_tsquery('attributevalue');

SELECT * FROM fts.xmldocument WHERE
to_tsvector(regexp_replace(content::text,'[<>/]',' ','g')) @@ to_tsquery('boo2');

SELECT * FROM fts.xmldocument WHERE
to_tsvector(regexp_replace(content::text,'[<>/]',' ','g')) @@ to_tsquery('boo4');

Ao solucionar outro problema , notei que no meu sistema Ubuntu 14.04 existem dois postgresqldiretórios contendo um postgresql.confarquivo:

• /usr/share/postgresql
• /etc/postgresql/9.3/main/

Com a seguinte estrutura de diretórios:

$ tree -d /usr/share/postgresql
/usr/share/postgresql
├── 9.3
│   ├── extension
│   ├── man
│   │   ├── man1
│   │   └── man7
│   ├── timezonesets
│   └── tsearch_data
└── 9.4
    └── man
        ├── man1
        └── man7

... e:

$ tree -d /etc/postgresql/9.3/main/
/etc/postgresql/9.3/main/

Com os postgresql.confarquivos residindo nos seguintes locais:

• /usr/share/postgresql/9.3/postgresql.conf
• /etc/postgresql/9.3/main/postgresql.conf

Ao usar SHOW config_file;, verifiquei qual arquivo de configuração meu servidor está usando:

postgres=# SHOW config_file;
               config_file                
------------------------------------------
/etc/postgresql/9.3/main/postgresql.conf
(1 row)

Minha pergunta é por que encontro o postgresql.confarquivo em dois locais e o acima é uma indicação de alguma instalação não utilizada ou outros artefatos não utilizados que devo limpar?

Eu segui as instruções dadas abaixo:

http://www.postgresql.org/docs/9.3/static/pgstatstatements.html

... para o efeito de adicionar as seguintes linhas:

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

No arquivo postgresql.confe reiniciando o servidor, mas a tabela pg_stat_statementsainda não está visível:

$ cat /usr/share/postgresql/9.3/postgresql.conf | grep -A3 ^shared_preload_libraries
shared_preload_libraries = 'pg_stat_statements' # (change requires restart)
pg_stat_statements.max = 10000
pg_stat_statements.track = all


$ sudo /etc/init.d/postgresql restart
* Restarting PostgreSQL 9.3 database server          [ OK ] 

$ psql -U postgres
psql (9.3.10)
Type "help" for help.

postgres=# select count(*) from pg_stat_activity;
count 
-------
      1
(1 row)

postgres=# select count(*) from pg_stat_statements;
ERROR:  relation "pg_stat_statements" does not exist

atualizar

Depois de executar:

sudo apt-get install postgresql-contrib-9.3

E então fazendo:

$ psql -U postgres
psql (9.4.5, server 9.3.10)
Type "help" for help.

postgres=# create extension pg_stat_statements;
CREATE EXTENSION
postgres=# \dx
                                     List of installed extensions
         Name        | Version |   Schema   |                        Description                        
--------------------+---------+------------+-----------------------------------------------------------
 pg_stat_statements | 1.1     | public     | track execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
 (2 rows)

 postgres=# \quit
$ sudo /etc/init.d/postgresql restart
 * Restarting PostgreSQL 9.3 database server  [OK]

... Agora eu entendo:

postgres=# select * from pg_stat_statements ;
ERROR:  pg_stat_statements must be loaded via shared_preload_libraries

detalhes do sistema

Eu estou correndo Ubuntu 14.04.03 LTS. PostgreSQL foi instalado com apt-get install.

rastreamento de log do PostgreSQL durante uma reinicialização

Ao fazer um sudo /etc/init.d/postgresql restart, recebo o seguinte rastreamento de log:

$ tail -f /var/log/postgresql/postgresql-9.3-main.log
2015-12-21 11:11:31 EET [23790-2] LOG:  received fast shutdown request
2015-12-21 11:11:31 EET [23790-3] LOG:  aborting any active transactions
2015-12-21 11:11:31 EET [7231-1] esavo-user@RegTAP FATAL:  terminating connection due to administrator command
2015-12-21 11:11:31 EET [23903-5] postgres@postgres FATAL:  terminating connection due to administrator command
2015-12-21 11:11:31 EET [23822-7] esavo-user@RegTAP FATAL:  terminating connection due to administrator command
2015-12-21 11:11:31 EET [23795-2] LOG:  autovacuum launcher shutting down
2015-12-21 11:11:31 EET [23815-1] esavo-user@RegTAP FATAL:  terminating connection due to administrator command
2015-12-21 11:11:31 EET [23792-1] LOG:  shutting down
2015-12-21 11:11:31 EET [23792-2] LOG:  database system is shut down
2015-12-21 11:11:32 EET [16886-1] LOG:  database system was shut down  at 2015-12-21 11:11:31 EET
2015-12-21 11:11:32 EET [16886-2] LOG:  MultiXact member wraparound  protections are now enabled
2015-12-21 11:11:32 EET [16885-1] LOG:  database system is ready to accept connections
2015-12-21 11:11:32 EET [16890-1] LOG:  autovacuum launcher started
2015-12-21 11:11:33 EET [16892-1] [unknown]@[unknown] LOG:  incomplete startup packet

Estes são resultados reais do meu banco de dados. Passei algum tempo tentando reproduzir esse comportamento com um SSCCE, mas falhei. Como o último dos resultados abaixo é possível?

SELECT COUNT(*) FROM rr.resource    a
--- 15771

SELECT COUNT(*) FROM rr.resource    a
NATURAL JOIN rr.interface   b
--- 41419

SELECT COUNT(*) 
FROM rr.resource    a
NATURAL JOIN rr.interface   b
NATURAL JOIN rr.interface   c
--- 0

A contagem de linha zero (0) na última consulta é consistente e repetível todas as vezes. Em alguns exemplos que criei, adicionar uma segunda junção natural com a mesma tabela duas vezes resulta no mesmo número de linhas (como eu esperava). Como então o resultado acima é possível?

Como as senhas são codificadas na coluna passwdde pg_shadow?

Fazendo um:

select passwd from pg_shadow;

Eu vejo valores como o seguinte:

 md52c92c858aa1fa12144f1dcbd4ca9c3a0

... o que não parece ser o que eu sei que o hash MD5 da minha senha é (retirando o "md5" principal, obviamente). Os hashes também são salgados?

Estou seguindo um guia de instalação do PostgreSQL que aconselha (após a instalação do banco de dados pela primeira vez), configurar uma senha para o postgresusuário:

alter role postgres password '<<super-secret-password>>';

... e, em seguida, altere o pg_hba.confarquivo para que a linha para soquetes locais (Unix) seja:

local all all md5

... ao invés de:

local all all trust

Parece-me que isso apenas adiciona uma camada extra de defesa, mas para alguém tirar vantagem local all all trust, ele primeiro teria que obter acesso à máquina local, certo?

Pergunta 1: entendi corretamente que, se o acesso à máquina local for protegido por senha (senha UNIX), não haverá preocupações com um local all all trust?

Além disso, o mesmo guia também recomenda que um .pgpassarquivo esteja localizado em minha pasta pessoal (acho que para scripts de backup que não podem ser solicitados a fornecer uma senha). A senha é armazenada em texto não criptografado nesse ~/.pgpassarquivo:

echo 'localhost:5432:*:postgres:<<super-secret-password>>' > .pgpass
chmod 0600 ~/.pgpass

Pergunta 2: Então, qual é o benefício de usar md5e não trustpara soquetes locais? Se alguém obtiver acesso à máquina local, ele também poderá ler o .pgpassarquivo. Estou perdendo alguma coisa?

Entendo que, após a instalação, o PostgreSQL não possui senha para seu usuário root do banco de dados (postgres):

postgres=# select usename, passwd is null from pg_shadow;
 usename  | ?column? 
----------+----------
 postgres | t
(1 row)

... e é aconselhável configurá-lo com:

alter role postgres password '<<very-secret>>';

(e, em seguida, atualize o pg_hba.confarquivo de acordo)

Minha pergunta é: qual é o SQL a ser usado para voltar à situação anterior quando nenhuma senha era necessária para user postgres.

Em geral, como posso remover o requisito de senha para qualquer função? Não estou perguntando como alterar a senha, mas como remover o requisito de senha ( passwdcoluna nula na tabela pg_shadow).

Entendo que no PostgreSQL, se houver um FK de várias colunas de tabela Bpara tabela Ae uma ou mais colunas FK na tabela Bforem anuláveis, o FK ainda será considerado "honrado" para as linhas em Bque o valor dessas colunas é de fato NULL sem verificação da presença de uma linha correspondente na tabela A(já que as colunas de chave primária não podem ser anuladas):

SQL Fiddle

No entanto, isso é específico do PostgreSQL ou está no padrão SQL? (experimentando o SQL Fiddle, parece que tanto o MS SQL Server quanto o MySQL se comportam da mesma maneira).

Eu tenho um aplicativo que (como parte de sua lógica) corta strings e substitui strings vazias por NULL antes da inserção no banco de dados. Acho que uma maneira de garantir que isso seja aplicado seria escrever um CHECK em todas as tabelas que possuem uma coluna VARCHAR, (ou similar).TEXT

Supondo que alguém não possa ou não queira fazer isso, existe uma maneira de escrever uma consulta SQL simples e genérica (obtendo nomes de tabelas e colunas dos metadados do banco de dados) que verificaria se alguma coluna de texto no banco de dados contém strings vazias ?

SSCCE: O seguinte script:

$ cat test.sql 
CREATE TABLE public.foo (
loop INTEGER
);

CREATE OR REPLACE FUNCTION public.foo_fun(loop INTEGER) RETURNS BOOLEAN AS $$
    SELECT TRUE;
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION public.foo_tr_fun() RETURNS TRIGGER AS $$
DECLARE
    fRV BOOLEAN;
BEGIN
    SELECT public.foo_fun(NEW.loop) INTO fRV;
IF fRV THEN
    RETURN NEW;
ELSE
    RAISE EXCEPTION 'bar';
END IF;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER foo_tr AFTER INSERT OR UPDATE ON public.foo
FOR EACH ROW
EXECUTE PROCEDURE public.foo_tr_fun();

... com o qual corro:

psql -v ON_ERROR_STOP=1 --quiet -X -U some-user -d some-db -f test.sql

... quando tento inserir uma linha em foo(de psql):

$ psql -U some-user some-db
psql (9.1.14)
Type "help" for help.

RegTAP=> SELECT * FROM public.foo;
 loop 
------
(0 rows)

RegTAP=> INSERT INTO public.foo(loop) VALUES(0);
ERROR:  record "new" has no field "loop"
LINE 1: SELECT public.foo_fun(NEW.loop)
                              ^
QUERY:  SELECT public.foo_fun(NEW.loop)
CONTEXT:  PL/pgSQL function "foo_tr_fun" line 5 at SQL statement
RegTAP=> 

Eu emito o seguinte DDL:

CREATE TABLE foo
(
loopa INTEGER,
loop  INTEGER
);

Em seguida, examinando o DDL no servidor (usando uma ferramenta do lado do cliente como DbVisualizer - desculpe, não conheço a consulta para produzi-lo diretamente), vejo o seguinte:

CREATE TABLE
    foo
    (
        loopa INTEGER,
        LOOP INTEGER
    );

Observe a mudança de maiúsculas e minúsculas para a coluna nomeada, loopmas não para a nomeada loopa. Olhando para a lista de palavras- chave SQL loop não parece ser uma palavra-chave. Por que então essa mudança seletiva?

Isso parece estar relacionado a um problema que estou tendo com um gatilho tentando acessar uma coluna loopem uma tabela onde recebo:

Exception in thread "main" org.postgresql.util.PSQLException: ERROR: record "new" has no field "loop"

(onde recebo o mesmo erro independentemente de o código na função do gatilho acessar o campo como NEW.LOOPou NEW.loop)

ATUALIZAR

As maiúsculas parecem ser um artefato do DbVisualizer. psqlmostra:

psql (9.1.13)
Type "help" for help.

foo-test=> \dS+ foo
                    Table "test.foo"
 Column |  Type   | Modifiers | Storage | Description 
--------+---------+-----------+---------+-------------
 loopa  | integer |           | plain   | 
 loop   | integer |           | plain   | 
Has OIDs: no

No entanto, isso ainda não explica por que o mecanismo PostgreSQL aparentemente não permite que meu gatilho acesse arquivos NEW.loop. Quando renomeio a coluna (e o código do gatilho) para loopitudo parece funcionar bem. Portanto, provavelmente há algo mais profundo em ação aqui do que apenas uma falha do DbVisualizer. Vou tentar criar um SSCCE mais tarde.

ATUALIZAÇÃO 2

Um SSCCE para o problema do acionador foi criado aqui .

Este é obviamente um SSCCE.

Eu tenho uma inventorytabela que contém o número de itens ( numOfItems) em um depósito em uma determinada data ( inventoryDate). Ambos são números inteiros para manter as coisas simples:

CREATE TABLE inventory(inventoryDate INTEGER, numOfItems INTEGER);
ALTER TABLE inventory ADD PRIMARY KEY (inventoryDate);

Agora eu tenho algumas entradas:

INSERT INTO inventory(inventoryDate, numOfItems) VALUES(1,250),(2,275)

O que foi dito acima é que pontualmente 1havia 250 itens no depósito e 2pontualmente 275.

Agora quero testar se, na última data, o número de itens no depósito ultrapassou 1000:

SELECT 1000<(SELECT numOfItems FROM inventory ORDER BY inventoryDate DESC LIMIT 1)

O acima parece funcionar. No entanto, no caso extremo em que não há entradas na inventorytabela, não está funcionando:

DELETE FROM inventory;
SELECT 1000<(SELECT numOfItems FROM inventory ORDER BY inventoryDate DESC LIMIT 1)

... retorna:

?column?
(null)

Eu gostaria de tratar o caso extremo em que não há entradas como denotando zero (0) itens. Eu sei que sempre posso inicializar a inventorytabela com uma primeira entrada falsa com um valor zero, mas como eu disse, este é um SSCCE (no meu caso real, esta tabela é realmente uma exibição que não desejo modificar).

Então, acabo escrevendo a consulta assim:

WITH cte AS (SELECT numOfItems FROM inventory ORDER BY inventoryDate DESC LIMIT 1)
SELECT 1000 < (
CASE 
WHEN NOT EXISTS(SELECT 1 FROM cte) THEN 0
         ELSE  (SELECT numOfItems FROM cte)
END)

O acima parece funcionar. Mas, existe uma maneira mais idiomática? Algo como um COALESCEmas para um conjunto de resultados vazio em vez de nulo?

Estou despejando meu banco de dados Postgres usando pg_dump (em formato de texto simples) e, em seguida, restaurando-o simplesmente usando psql (com a -fopção).

O que levanta a questão: estou perdendo alguma coisa por não usar o pg_restore , que parece uma ferramenta de restauração especializada (em comparação com o genérico psql)?

Posso controlar opções como desabilitar gatilhos e outros usando pg_dumpparâmetros. Para que serve, então pg_restore? formatos de despejo de texto não simples ?