Introdução
No PostgreSQL existem várias maneiras de gerenciar Objetos Binários Grandes (LOB, BLOB):
- binário base de dados tipo BYTEA
- Basic tipo de dados de caractere de TEXTO
- objectos Grandes (LO) facilidade pg_largeobject’
- tipo de Dados DATALINK (que é apenas spec., nenhuma implementação)
o PostgreSQL também suporta um sistema de armazenamento chamado “TOAST” (a técnica de armazenamento de atributos superdimensionados) que armazena automaticamente valores maiores que uma única página de banco de dados (normalmente 8 KB) em uma área de armazenamento secundária por tabela. Isso define o limite de tamanho de qualquer coluna/campo para 1 GB.
Aqui estão algumas perguntas de design e implementação sobre objetos grandes:
- o LO (conceitualmente) é parte do objeto/entidade – ou apenas associado a ele?
- como o LO é acessado: como string codificada, como arquivo ou baseado em fluxo?
- se não estiver armazenado dentro da tabela, quem mantém a integridade do LO?
Importar/Exportar arquivos binários externos
ao usar BYTEA e TEXT (e XML), a maneira comum de importar/exportar arquivos externos é dentro do cliente (por exemplo, Java/Python) ou programas serverside (por exemplo, PL/Python). É difícil lidar com arquivos externos com psql.
Ver Stackexchange:.
BYTEA
o tipo de dados bytea permite o armazenamento de strings Binárias.
- armazena um LOB dentro da tabela, respectivamente usando brinde.
- é, portanto, limitado a 1 GB
- o armazenamento é octal e permite caracteres não imprimíveis (em contraste com strings de caracteres que não).
- o formato de entrada / saída é HEX (a partir do PostgreSQL 9.0).
notas:
- BYTEA chega perto do tipo de string binária padrão SQL ‘BLOB’. As funções e operadores fornecidos pelo BYTEA são principalmente os mesmos, enquanto o formato de entrada hexadecimal do BYTEA é diferente.
- BYTEA é mais lento para comprimentos > 20 MB do que a instalação LO (não tem accees aleatórios).
veja os tipos de dados binários do PostgreSQL doc.
texto
texto básico do tipo de dados está aqui apenas para completude. Este é um tipo de caractere variável com comprimento ilimitado (até 1 GB). os tipos de caracteres permitem configurações de localidade.
não é uma string de bytes, mas ainda se pode usá-la quando a string binária é pré-processada e codificada em forma imprimível (por exemplo, base64 ou hex).
instalação de objeto Grande (LO)
objetos grandes (LO) também podem ser colocados em uma única tabela do sistema chamada ‘pg_largeobject’ que deve ser acessada por meio de identificadores do tipo de dados OID.
- existe uma API de leitura / gravação que oferece funções client (= modules written in C) e server-side (=SQL).
- as principais funções SQL relacionadas são: lo_creat(), lo_create(), lo_unlink(), lo_import () e lo_export (). lo_import () e lo_export() precisam de permissões do usuário proprietário do banco de dados (ou seja, superusuário).
- LO são divididos em” pedaços ” e armazenados em linhas indexadas btree.
- LO permite valores de até 2 GB de tamanho, enquanto campos torrados (como BYTEA) podem ter no máximo 1 GB.
- as entradas LO podem ser modificadas aleatoriamente usando uma API de leitura / gravação que é mais eficiente do que executar essas operações usando o TOAST (e, por exemplo, o BYTEA).
Nota:
- quando PostgreSQL doc. menciona ‘ lo ‘(lo = objeto grande) normalmente se refere a essa facilidade.
- em contraste com, por exemplo, BYTEA – LO não é um tipo de dados por conta própria, mas uma tabela, uma ‘facilidade’.
Nota importante ao usar o BLOB JDBC (ou a anotação @ Lob no Hibernate):
- como o PostgreSQL considera uma entrada LO como um objeto por conta própria, excluir ou atualizar linhas na tabela do Usuário não exclui ou exclui entradas em pg_largeobjects. pg_largeobjects, portanto, cresce infinitamente, a menos que uma limpeza separada seja feita (consulte este relatório de erro no fórum Hibernate).
- para evitar isso, normalmente um gatilho precisa ser adicionado, o que exclui entradas em pg_largeobject como descriebd no módulo ‘lo’.
- veja os módulos adicionais do PostgreSQL ‘ lo ‘e’ vaccumlo ‘ nos documentos do PostgreSQL.
veja PostgreSQL doc ‘objetos grandes’ e JDBC Tipo de dados BLOB: .
exemplo: uso típico em SQL (com base em documentos Postgres):
CREATE TABLE image ( id integer, name text, picture oid ); SELECT lo_creat(-1); -- returns OID of new, empty large object. SELECT lo_create(43213); -- attempts to create large object with OID 43213. SELECT lo_unlink(173454); -- deletes large object with OID 173454. INSERT INTO image (id, name, picture) VALUES (1, 'beautiful image', lo_import('/etc/motd')); INSERT INTO image (id, name, picture) -- same as above, but specify OID to use. VALUES (1, 'beautiful image', lo_import('/etc/motd', 68583)); SELECT lo_export(image.raster, '/tmp/motd') FROM image -- need superuser permission. WHERE name = 'beautiful image';
DATALINK
nota: atualmente não há implementação no PostgreSQL para isso. É apenas uma especificação definida no SQL padrão ‘SQL / MED’.
o tipo DATALINK armazena URLs de arquivos em colunas de banco de dados e aplica restrições a ele.
- mantém um link para um arquivo específico no armazenamento externo.
- o sistema de banco de dados assume o controle sobre arquivos externos (renomear, excluir, as permissões são feitas com SQL) se definido assim.
- o tamanho do arquivo é ilimitado, respectivamente limitado pelo armazenamento externo. Não há necessidade de armazenar o conteúdo do arquivo no sistema de banco de dados.
parâmetros DATALINK:
- nenhum valor de Datalink de controle de LINK não precisa fazer referência a um arquivo/URL existente.
- FILE LINK CONTROL O valor do Datalink deve fazer referência a um arquivo/URL existente.Integridade todos os arquivos referenciados só podem ser renomeados ou excluídos por meio do SQL.
- integridade os arquivos referenciados seletivos podem ser renomeados ou excluídos por meio de SQL ou diretamente.
- integridade nenhum (implícito para nenhum controle de LINK)
- no UNLINK Excluir Arquivo é excluído do sistema de arquivos quando excluído do banco de dados.
- em UNLINK restaurar permissões originais do arquivo são restaurados quando excluídos do banco de dados.
- no UNLINK NONE nenhuma alteração nas permissões de arquivo quando a referência do arquivo é excluída do banco de dados.
- recuperação sim PITR aplica-se a arquivos referenciados.
- recuperação nenhum PITR não se aplica a arquivos referenciados.
estado e instalação:
- não está claro.
Exemplo:
CREATE TABLE image ( id integer, name text, picture DATALINK ); INSERT INTO persons VALUES ( 1, 'Jon Doe', DLVALUE('file://some/where/1.jpg') );
Weblinks:
- DATALINK página wiki
- Apresentação de Pedro Eisentraut, PGCon 2009:
- MSE-Seminário de Tese (2011) de Florian Schwendener sobre “SQL/MED e Mais – Gerenciamento de Dados Externos no PostgreSQL e Microsoft SQL Server”: